Solved

VBA SQL Query

Posted on 2013-10-25
2
284 Views
Last Modified: 2013-10-26
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
Comment
Question by:Knowknot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39602029
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39602300
:) thank you, must have guessed at least partly right. Cheers, Paul
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question