Put a variable in a query parameter?

Question

Hi everyone,

Is it possible to use a "variable" field you have just created in a query, as a term for other columns in that query? Using Access 2013 query design.

I am pulling out dates as text that might be written like "8-1-2010 1:00" or might be "12-30-2010 17:35". The position of the dashes and spaces is important to parsing. If I can put the position in a variable(s), it makes other equations easier. For example have a field say:

DashPos1: InStr( [Timestamp], "-"])

DashPos2: InStr( DashPos1+1, [Timestamp], "-")

Then it makes equations easier. Say I want to turn those timestamps into e.g. a date field as YYYY-M-D:

= mid(DashPos2+1, [Timestamp], 4) & "/" & mid( [TimeStamp, DashPos1-1) & "/" & mid(DashPos1+1, [Timestamp], DashPos2-DashPos1 )

This equation is probably not 100% correct but don't worry about making it correct; you see what I'm trying to do, and how the "variables" make the date equation a lot shorter and a lot easier mentally (to not have commas and parens in the wrong place of a god-awful long equation, etc.)

If I try the equation above, it simply considers the variable terms as text strings themselves (it puts quote marks around them). Of course, that's not going to work at all.

I know that in SAS you can make a variable anywhere by putting && in front of some term, and then this gets "turned into" whatever the variable stood for when you run the SAS code. Wow, you could do some really funky things with that (variables that parsed into other variables or other code, etc.). But then SAS is closer to real code than the Access query window.

I've seen and used SQL and the SQL query view before, and can probably handle a SQL answer.

Please note, I am a data hobbyist and am always dealing with new datasets of different types. I am not making an app and nothing is fixed. So please, no VBA or other complicated superstructures. I am just and only asking, straight up, is there some sort of way to have a variable within a query, that was created right in that query.

Otherwise I guess the worse case is I have to build a second query on top of the first one (or make new fields) with those new variables. And yes I can do that. But that's not the answer I'm looking for. The question is, do I have to? Or can I avoid making extra queries and fields, and just directly make a value and use it within my one query somehow, already.

Thanks if you can help!

Answer 1

............the core question still would have been interesting to hear, if "variables" were possible. But you say that there basically isn't any kind of "dynamic" variable of the type I mean, eh? Where you can create and use it, all in the one same query? (To simplify long expressions)

You can do the sort of thing you illustrated in your examples in VBA, but not in an SQL statement.  You can of course wrap the VBA code in a Public function in a standard module and call the function in a query, passing the value(s) of relevant column(s) into the function as argument(s).

Answer 2

With the dates as string expressions which you have cited, there is no need whatsoever to parse the string expression.  You can do one of two things:

1.  If the database is being used solely on systems which use the US short date then you can call the CDate function to return the value as a date/time data type. This can then be formatted however you wish by means of the Format function, e.g. to return the date in the ISO standard format for data notation:  Format(CDate("8-1-2010 1:00"),"yyyy-mm-dd").

2.  If the database is also to be used on systems where the regional date format differs from the US format, but the string expressions are in US date format, you can build a date literal by wrapping the value in # characters and calling the Eval function.

You can see the difference  in the debug window when used here in the UK where the short date format is dd/mm/yyyy

? CDate("8-1-2010 1:00")

08/01/2010 01:00:00

? Eval("#" & "8-1-2010 1:00" & "#")

01/08/2010 01:00:00

The CDate function incorrectly assumes the value is in the local regional date format, so returns a date/time value which assumes the string expression to be in the UK format.  The second returns the value correctly by evaluating the date literal.  Date literals must be in US short date format or an otherwise internationally unambiguous format such as the ISO standard.

BTW what we are talking about here are expressions not equations.  An equation, mathematically speaking, is a formula affirming the equivalence of two symbolic or numerical expressions (indicated by the sign =).  An expression, mathematically, is a collection of symbols that jointly express a quantity.  In database terms an expression is similar in concept to its mathematical use, the 'symbols' being whatever code elements in the expression contribute to the value to which the expression evaluates.

As regards your original question in general terms rather than in the context of the specific examples which you've cited, you can in some cases reference a computed column by name in an SQL statement, in other cases not, and even then it might not be consistent across different flavours of SQL. The SQL-99 standard for instance allowed a computed column's name to be used in an ORDER BY clause, but in JET/ACE SQL as used by Access the expression has to be used in the ORDER BY clause.

Answer 3

Thanks Ken, wow, I've been doing Access queries for a dozen years but never once used CDate because I figured it was going for certain odd byte conversion situations, not simply for parsing/importing date/time text per se. But it worked like a charm. Very good to know. Thanks!

I'll try to keep your note about expressions in mind. I guess I always knew that, but never thought it mattered. Still, why be technically incorrect all the time, when all one has to do is use a different word, and then they're technically correct all the time?  :)

Past the above, the core question still would have been interesting to hear, if "variables" were possible. But you say that there basically isn't any kind of "dynamic" variable of the type I mean, eh? Where you can create and use it, all in the one same query? (To simplify long expressions)