Runtime prompt for date value

  • Thread starter Thread starter Ken Eisman
  • Start date Start date
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
 
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.
 
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...
 
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...
 
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...
 
Back
Top