Runtime prompt for date value

K

Ken Eisman

This is probably something real easy that I'm overlooking.

I'm trying to run a query that prompts me for a date at runtime and give me
all the records prior to that date.

Here's the SQL query:
SELECT [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].DescriptionFld, [TR QRY JOB INVENTORY].EmployeeNbr, [TR QRY JOB
INVENTORY].PRADDEmployeeNameLast, [TR QRY JOB
INVENTORY].PRADDEmployeeNameFirst, [TR QRY JOB INVENTORY].PRMISSSN, [TR QRY
JOB INVENTORY].PRMISDOB, [TR QRY JOB INVENTORY].PRMISHireDate,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate,
[sqryEBNFT:EmployeeInsurBenefits].CancelDate
FROM [TR QRY JOB INVENTORY] INNER JOIN [sqryEBNFT:EmployeeInsurBenefits] ON
[TR QRY JOB INVENTORY].EmployeeNbr =
[sqryEBNFT:EmployeeInsurBenefits].EmployeeNbr

**** Trouble area
WHERE ((([sqryEBNFT:EmployeeInsurBenefits].EnrollDate)<[Enroll Date]))
**** End Trouble area

ORDER BY [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].EmployeeNbr, [TR QRY JOB INVENTORY].PRADDEmployeeNameLast,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate DESC;

If I replace the [Enroll Date] prompt with a date #12/01/2004#, the query
works. Whenever I try to put the same date 12/01/2004 in the dialogue box at
runtime, the date comparison does not work.

Any help would be appreciated.

Ken
 
D

David Ferguson

In the criteria for the date field you wish to query enter the following

[Enter Date]

This will give you a dialog box at runtime with the text "Enter Date" and
you simply add the date into the text field in the dialog box.
You can of course change the text within the criteria if you wish.
 
K

Ken Eisman

That's what I've done (except I called it [Enroll Date] instead of [Enter
Date]). When I use this dialogue box, the comparison does not seem to work
(i.e. I get all the records). If I replace the prompt in the criteria with a
literal (#12/01/2004#), the comparison works (i.e. I only get the records
prior to 12/1/04).

My actual criteria field looks like this:

<[Enroll Date]

That doesn't work.

If I change the criteria field to:

<#12/01/2004#

It works.

David Ferguson said:
In the criteria for the date field you wish to query enter the following

[Enter Date]

This will give you a dialog box at runtime with the text "Enter Date" and
you simply add the date into the text field in the dialog box.
You can of course change the text within the criteria if you wish.


Ken Eisman said:
This is probably something real easy that I'm overlooking.

I'm trying to run a query that prompts me for a date at runtime and give me
all the records prior to that date.

Here's the SQL query:
SELECT [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].DescriptionFld, [TR QRY JOB INVENTORY].EmployeeNbr, [TR QRY
JOB
INVENTORY].PRADDEmployeeNameLast, [TR QRY JOB
INVENTORY].PRADDEmployeeNameFirst, [TR QRY JOB INVENTORY].PRMISSSN, [TR QRY
JOB INVENTORY].PRMISDOB, [TR QRY JOB INVENTORY].PRMISHireDate,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate,
[sqryEBNFT:EmployeeInsurBenefits].CancelDate
FROM [TR QRY JOB INVENTORY] INNER JOIN [sqryEBNFT:EmployeeInsurBenefits] ON
[TR QRY JOB INVENTORY].EmployeeNbr =
[sqryEBNFT:EmployeeInsurBenefits].EmployeeNbr

**** Trouble area
WHERE ((([sqryEBNFT:EmployeeInsurBenefits].EnrollDate)<[Enroll Date]))
**** End Trouble area

ORDER BY [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].EmployeeNbr, [TR QRY JOB INVENTORY].PRADDEmployeeNameLast,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate DESC;

If I replace the [Enroll Date] prompt with a date #12/01/2004#, the query
works. Whenever I try to put the same date 12/01/2004 in the dialogue box at
runtime, the date comparison does not work.

Any help would be appreciated.

Ken
Click to expand...
Click to expand...
 
G

Guest

Hi Ken,

Try
< CDate([Enroll Date])

"CDate" function will convert any date string into date type.


Ken Eisman said:
That's what I've done (except I called it [Enroll Date] instead of [Enter
Date]). When I use this dialogue box, the comparison does not seem to work
(i.e. I get all the records). If I replace the prompt in the criteria with a
literal (#12/01/2004#), the comparison works (i.e. I only get the records
prior to 12/1/04).

My actual criteria field looks like this:

<[Enroll Date]

That doesn't work.

If I change the criteria field to:

<#12/01/2004#

It works.

David Ferguson said:
In the criteria for the date field you wish to query enter the following

[Enter Date]

This will give you a dialog box at runtime with the text "Enter Date" and
you simply add the date into the text field in the dialog box.
You can of course change the text within the criteria if you wish.


Ken Eisman said:
This is probably something real easy that I'm overlooking.

I'm trying to run a query that prompts me for a date at runtime and give me
all the records prior to that date.

Here's the SQL query:
SELECT [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].DescriptionFld, [TR QRY JOB INVENTORY].EmployeeNbr, [TR QRY
JOB
INVENTORY].PRADDEmployeeNameLast, [TR QRY JOB
INVENTORY].PRADDEmployeeNameFirst, [TR QRY JOB INVENTORY].PRMISSSN, [TR QRY
JOB INVENTORY].PRMISDOB, [TR QRY JOB INVENTORY].PRMISHireDate,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate,
[sqryEBNFT:EmployeeInsurBenefits].CancelDate
FROM [TR QRY JOB INVENTORY] INNER JOIN [sqryEBNFT:EmployeeInsurBenefits] ON
[TR QRY JOB INVENTORY].EmployeeNbr =
[sqryEBNFT:EmployeeInsurBenefits].EmployeeNbr

**** Trouble area
WHERE ((([sqryEBNFT:EmployeeInsurBenefits].EnrollDate)<[Enroll Date]))
**** End Trouble area

ORDER BY [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].EmployeeNbr, [TR QRY JOB INVENTORY].PRADDEmployeeNameLast,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate DESC;

If I replace the [Enroll Date] prompt with a date #12/01/2004#, the query
works. Whenever I try to put the same date 12/01/2004 in the dialogue box at
runtime, the date comparison does not work.

Any help would be appreciated.

Ken
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Eisman

That seems to have worked!

Thanks
Ken

JL said:
Hi Ken,

Try
< CDate([Enroll Date])

"CDate" function will convert any date string into date type.


Ken Eisman said:
That's what I've done (except I called it [Enroll Date] instead of [Enter
Date]). When I use this dialogue box, the comparison does not seem to
work
(i.e. I get all the records). If I replace the prompt in the criteria
with a
literal (#12/01/2004#), the comparison works (i.e. I only get the records
prior to 12/1/04).

My actual criteria field looks like this:

<[Enroll Date]

That doesn't work.

If I change the criteria field to:

<#12/01/2004#

It works.

David Ferguson said:
In the criteria for the date field you wish to query enter the
following

[Enter Date]

This will give you a dialog box at runtime with the text "Enter Date"
and
you simply add the date into the text field in the dialog box.
You can of course change the text within the criteria if you wish.


This is probably something real easy that I'm overlooking.

I'm trying to run a query that prompts me for a date at runtime and
give
me
all the records prior to that date.

Here's the SQL query:
SELECT [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].DescriptionFld, [TR QRY JOB INVENTORY].EmployeeNbr, [TR QRY
JOB
INVENTORY].PRADDEmployeeNameLast, [TR QRY JOB
INVENTORY].PRADDEmployeeNameFirst, [TR QRY JOB INVENTORY].PRMISSSN,
[TR
QRY
JOB INVENTORY].PRMISDOB, [TR QRY JOB INVENTORY].PRMISHireDate,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate,
[sqryEBNFT:EmployeeInsurBenefits].CancelDate
FROM [TR QRY JOB INVENTORY] INNER JOIN
[sqryEBNFT:EmployeeInsurBenefits]
ON
[TR QRY JOB INVENTORY].EmployeeNbr =
[sqryEBNFT:EmployeeInsurBenefits].EmployeeNbr

**** Trouble area
WHERE ((([sqryEBNFT:EmployeeInsurBenefits].EnrollDate)<[Enroll Date]))
**** End Trouble area

ORDER BY [TR QRY JOB INVENTORY].Department, [TR QRY JOB
INVENTORY].EmployeeNbr, [TR QRY JOB INVENTORY].PRADDEmployeeNameLast,
[sqryEBNFT:EmployeeInsurBenefits].EnrollDate DESC;

If I replace the [Enroll Date] prompt with a date #12/01/2004#, the
query
works. Whenever I try to put the same date 12/01/2004 in the dialogue
box
at
runtime, the date comparison does not work.

Any help would be appreciated.

Ken
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OpenOffice.Org Newsletter - Vol. 01 Issue 9 3
WTD: Nvidia Videocard for 4x AGP Slot 0
http://drpcdr.ca 4
FWT Newsletter - Weekly - October 4, 2004 2

Top