VBA and SQL re:Calender variable

  • Thread starter Thread starter AndyB
  • Start date Start date
A

AndyB

I have a VBA code like this
' "Calender" is my ActiveX calender

Dim strqry As String
Dim eDate As Date
Dim sdate As Date
eDate = ([Calender] - 27)
sdate = [Calender]
This just checks the variables are set
MsgBox sdate
MsgBox eDate

' DoCmd.OpenQuery qryname
' try putting in SQL direct
strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"

strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=[sdate] And
([Mytable].[Date_Report])<=[edate]));"

DoCmd.RunSQL strqry


It does not work I get the correct dates from the message box but they are
not carried over to the query.
I even set the DIm to string but it does not work.
What am I missing

Andy
 
Hi,
You can't have the variables as part of the string, they become literal values.
I'm sure that none of the values in your Date_Report field are = sdate
They might be equal to the value of sdate however.
You have to let Access evaluate the value of your variables and then concatenate them
into your string (dates have to be delimited with #)

strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] & "#));"

Always issue a debug.Print statement before executing so you can see what your
sql string looks like.
 
I gave it a try and I still get an error that states

This object does not support this property or method.



Andy
 
Try printing out the SQL string you're using

strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"
strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] &
"#));"

Debug.Print strqry

DoCmd.RunSQL strqry

You'll find it's got some problems in it: you don't have any blanks between
[NewTable]FROM [Mytable]WHERE

And while I hate to argue with Dan, if your short date format happens to be
set to dd/mm/yyyy, then that code is going to fail anytime the day value for
sdate or edate is 1 through 12.

Try changing to:

strqry = "SELECT [Mytable].* INTO [NewTable] "
strqry = strqry & "FROM [Mytable] "
strqry = strqry & "WHERE ((([Mytable].[Date_Report])>=" & Format([sdate],
"\#mm\/dd\/yyyy\#") & _
" And ([Mytable].[Date_Report])<=" &
Format([edate], "\#mm\/dd\/yyyy\#")

just to be sure.
 
Hey Doug,
No need to argue, I agree with you :-)

--
HTH
Dan Artuso, Access MVP


Douglas J. Steele said:
Try printing out the SQL string you're using

strqry = "SELECT [Mytable].* INTO [NewTable]"
strqry = strqry + "FROM [Mytable]"
strqry = strqry + "WHERE ((([Mytable].[Date_Report])>=#" & [sdate] & _
"# And ([Mytable].[Date_Report])<=#" & [edate] &
"#));"

Debug.Print strqry

DoCmd.RunSQL strqry

You'll find it's got some problems in it: you don't have any blanks between
[NewTable]FROM [Mytable]WHERE

And while I hate to argue with Dan, if your short date format happens to be
set to dd/mm/yyyy, then that code is going to fail anytime the day value for
sdate or edate is 1 through 12.

Try changing to:

strqry = "SELECT [Mytable].* INTO [NewTable] "
strqry = strqry & "FROM [Mytable] "
strqry = strqry & "WHERE ((([Mytable].[Date_Report])>=" & Format([sdate],
"\#mm\/dd\/yyyy\#") & _
" And ([Mytable].[Date_Report])<=" &
Format([edate], "\#mm\/dd\/yyyy\#")

just to be sure.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



AndyB said:
I gave it a try and I still get an error that states

This object does not support this property or method.



Andy
Click to expand...
Click to expand...
 
Thanks all

It was tough going at first. I find that with all my programming:)


It works, I had to add two ))

Format([edate], "\#mm\/dd\/yyyy\#")


But it works.


Andy B
 
and just one more thing. More of a discussion then anything else. I
wanted to use that SQL in VBA because it was the way to go since I was using
a calender control in a form to access a range of dates. In some of my other
procedures I use VBA just to open the query up from the Queries I have
already written. Is there a general benefit one way or the other?


Andy B
 
Back
Top