• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:


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
    Case "Before"
        flg1STda = "<#" & Me.txtStartDate.Value & "#"    'Less than <#mm/dd/yyyy#
    Case "After"
        flg1STda = ">#" & Me.txtStartDate.Value & "#"    'Greater than >#mm/dd/yyyy#
    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?
  • 2
1 Solution
I'n not sure of this code syntax, but immediately after tblTest.[flag1date] you follow it with the equal sign, yet you are then trying to concatenate with strings for less than / greater than / between

so you would end up with

tblTest.[flag1date] =  #Me.txtStartDate.Value# -- this works

tblTest.[flag1date] =  <#Me.txtStartDate.Value#

tblTest.[flag1date] =  >#Me.txtStartDate.Value#

tblTest.[flag1date] =  Between #Me.txtStartDate.Value# And #Me.txtStartDate.Value#

that equal before a between isn't workable for sure

I think you need to remove the equal from the fixed string, and place the wanted operators into the case expression for all 4 options.

If this works without more effort it's a damn good guess.
:) thank you, must have guessed at least partly right. Cheers, Paul

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now