Help wiwth MS Query Syntax

[norrisar]

The database I am bumping up against expects date as #MM/DD/YYY# how do I use an argument, requesting input from user, for this in MS Query?

Anthony Norris

 

[SkipVought]

hi,

what database?

yyy? THAT's strange!

How do you EDIT the date that you user enters in a Texbox? NOT a particularly good approch! How about a Date Picker Control?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[norrisar]

First, the date format expected is #MM/DD/YYYY#, my bad. The hashtags are required. I am not positive, but I believe I am working with Oracle as the backend of a Trapeze installation.

I am automating a reporting process using MS Excel as the reporting tool with MS Query pulling the data. If the date range is entered directly into MS Query as ">=20111001 and <=20120630" MS Query automagically converts it to ">=#10/01/2011# and <=#06/30/2012#". However, I want to avoid having to edit the query each time the date range shifts. Like MS Access if I enter ">=[Start Date] and <=[End Date]" in the criteria field MS Query will pop up a dialog requesting the user to enter the appropriate dates. However, I have yet to find a suitable method of resolving the required hashtags. I have tried entering them directly in the dialog box, I have tried concantenating text strings, I have tried text formating functions. Nothing seems to work.

Any ideas?

Anthony Norris

 

[SkipVought]

I use MS Query a lot! Every day.

I doubt the MS Query will allow a >= PARAMETER.

I would use VBA without even a second thought.

alt+F11 toggles between the sheet and the VB Editor.

In the VB Editor enter this...

sub Query()
  dim sSQL as string

  with Activesheet.listobjects(1).querytable
     debug.print .commandtext
  end with
end with

then RUN this code to expost your SQL code in the Vide > Immediate Window

Paste back with the SQL that is in your Immediate Window, if you'd like to go that route. It is the best way to include user entered dates and such in the where clause.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]