Knowknot
asked on
VBA SQL Query
I'm using the following code to get a date value (or date values) from a text box, convert it to a string variable, and use it in a make table query. The code works fine for Case "Equals" but builds an empty table for all other values. If I take the variable and run it in a query by grid, it works fine for all Cases. Also I am unable to monitor the value of strSQL in a watch window. It shows a value of <Expression not defined in context> .
Select Case FlagDates 'Sets parameter format
Case "Equals"
flg1STda = Me.txtStartDate.Value
MakeTbl
Case "Before"
flg1STda = "<#" & Me.txtStartDate.Value & "#" 'Less than <#mm/dd/yyyy#
MakeTbl
Case "After"
flg1STda = ">#" & Me.txtStartDate.Value & "#" 'Greater than >#mm/dd/yyyy#
MakeTbl
Case "Between"
flg1STda = "Between #" & Me.txtStartDate.Value & "#" And "#" & Me.txtEndDate.Value & "#" 'Between #mm/dd/yyyy# and #mm/dd/yyyy#
Public Sub MakeTbl()
Dim strSQL As String
strSQL = "SELECT tblTest.* INTO TSTable FROM tblTest WHERE tblTest.[flag1date] = '" & flg1STda & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
What am I doing wrong?
Select Case FlagDates 'Sets parameter format
Case "Equals"
flg1STda = Me.txtStartDate.Value
MakeTbl
Case "Before"
flg1STda = "<#" & Me.txtStartDate.Value & "#" 'Less than <#mm/dd/yyyy#
MakeTbl
Case "After"
flg1STda = ">#" & Me.txtStartDate.Value & "#" 'Greater than >#mm/dd/yyyy#
MakeTbl
Case "Between"
flg1STda = "Between #" & Me.txtStartDate.Value & "#" And "#" & Me.txtEndDate.Value & "#" 'Between #mm/dd/yyyy# and #mm/dd/yyyy#
Public Sub MakeTbl()
Dim strSQL As String
strSQL = "SELECT tblTest.* INTO TSTable FROM tblTest WHERE tblTest.[flag1date] = '" & flg1STda & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
What am I doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:) thank you, must have guessed at least partly right. Cheers, Paul