INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!

Talk With Other Members
Be Notified Of Responses
To Your Posts
Keyword Search
One-Click Access To Your
Favorite Forums
Automated Signatures
On Your Posts
Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Tek-Tips LinkedIn^® Group

Join our
LinkedIn^® Group!

Hook up with past and present colleagues and classmates quickly.

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site

(Download This Button Today!)

Feedback

"...This is a very good site. Please keep it running. Thanks and wishing a great health and success for the site and its owners..."

More...

Geography

Where in the world do Tek-Tips members come from?

Click Here To Find Out!

Tek-Tips Shirts!

Get your
Tek-Tips Forums
SWAG here!

Home > Forums > MIS/IT > Office Suites > Microsoft: Office Forum

Help wiwth MS Query Syntax

Read More Threads Like This One

norrisar (IS/IT--Management)

19 Jul 12 15:25

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 (Programmer)

19 Jul 12 15:33

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,
_{Just traded in my old subtlety...

for a NUANCE!}

norrisar (IS/IT--Management)

19 Jul 12 15:54

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 (Programmer)

19 Jul 12 16:06

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...

CODE

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,
_{Just traded in my old subtlety...

for a NUANCE!}

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft: Office