Select Case Syntax

[mot98]

Hi All,

I have a M.S Access query that I am trying to use a variable to pull
current data, so in the criteria of the specific field. I am using the
Select Case command to populate the variable.

The code looks the following:

Select Case format(Time(),'hh:mm')
Case '"23:00' to "07:00"'
3
Case '"07:00" to "15:00"'
2
Case '"15:00" to "23:00"'

End Select


However I am getting the following error: "The syntax of the subquery
in this expression is incorrect. Check the subquerys syntax and enclose
the subquery in parenthese"

 

[Duane Hookom]

I wasn't aware that you could use SELECT CASE in the SQL of a query. Is
there a module/function in your issue somewhere?

 

[John Vinson]

Hi All,

I have a M.S Access query that I am trying to use a variable to pull
current data, so in the criteria of the specific field. I am using the
Select Case command to populate the variable.

The code looks the following:

Select Case format(Time(),'hh:mm')
Case '"23:00' to "07:00"'
3
Case '"07:00" to "15:00"'
2
Case '"15:00" to "23:00"'

End Select


However I am getting the following error: "The syntax of the subquery
in this expression is incorrect. Check the subquerys syntax and enclose
the subquery in parenthese"

Quite aside from the syntax, you'll get erroneous results. The Format
clause returns a TEXT STRING and you're compariing it with text
strings. If you're assuming that 05:00 is "between" "23:00" and
"07:00" - it *isn't*, since the comparison will be done
alphabetically, not chronologically.

The syntactical problem appears to be the extra ' marks. You have
three singlequotes in the first Case statement; and even in the
others, you're constructing a text string containing the doublequotes
and the times, and comparing that long text string to your time value.

Try instead:

Select Case Time()
Case >= #23:00# OR < #07:00# ' after 11pm or before 7am
Shift = 3
Case >= #15:00# AND < #23:00# ' between 3pm and 11pm
Shift = 2
Case >= #07:00# AND < #15:00# ' between 7am and 3pm
Shift = 1
End Select

Note also that the SELECT CASE syntax is allowed in VBA code, as in my
example, but *not* in the Access dialect of SQL. If you want this in a
Query you'll need to use the Switch() function instead:

Switch(Time() < #07:00# OR Time() >= #23:00#, 3, Time() >= #15:00# AND
Time() < #23:00#, 2, True, 1)


John W. Vinson[MVP]