Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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?
0
Knowknot
Asked:
Knowknot
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
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.
0
 
PortletPaulfreelancerCommented:
:) thank you, must have guessed at least partly right. Cheers, Paul
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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