query not working?!?

  • Thread starter Thread starter FSt1
  • Start date Start date
F

FSt1

i am trying to use an SQL statement in code to return a
date. if it returns null then skips an action. if it
returns a date then code should color a fond red.
but i cant get the statement to return anything but null.
what am i doing wrong.

Dim stgSQL As String
stgSQL = "SELECT tblHolidays.HDate FROM tblHolidays " & _
"WHERE (((tblHolidays.HDate)=[Forms]!
[frmCalendar]![txtD1]));"

thanks in advance.
 
FSt1 said:
i am trying to use an SQL statement in code to return a
date. if it returns null then skips an action. if it
returns a date then code should color a fond red.
but i cant get the statement to return anything but null.
what am i doing wrong.

Dim stgSQL As String
stgSQL = "SELECT tblHolidays.HDate FROM tblHolidays " & _
"WHERE (((tblHolidays.HDate)=[Forms]![frmCalendar]![txtD1]));"
Click to expand...
Hi FSt1,

DCount is equally as efficient (or more so)
and should be a part of your VBA "toolbox."

Dim stgSQL As String
Dim lngCnt As Long

If Not IsDate([Forms]![frmCalendar]![txtD1]) Then Exit Sub

lngCnt = DCount("*","tblHolidays","[HDate]=#" _
& [Forms]![frmCalendar]![txtD1] & "#")
If lngCnt >0 Then
'change "fond red"
Else
'skip it
End If

If you want to use your way, then you need to "tell"
Access the type of your parameter.

explicitly:

stgSQL = "PARAMETERS [Forms]![frmCalendar]![txtD1] DateTime;" _
& "SELECT tblHolidays.HDate FROM tblHolidays " & _
"WHERE (((tblHolidays.HDate)=[Forms]![frmCalendar]![txtD1]));"

or delimit with "#'s" as in the DCount example.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Back
Top