Entering date for parameter in expression field.

[irishhelp]

I have created an expression field in a query based on a date field and then added 28 days so it looks like this: 4 Week: [Referral Table]![Start Date]+28. The start date format in the Referral Table is: dd/mmm/yyyy and the input mask for this field is: 99\->L<LL\-0000;0;;;_. This is basically taking the Start Date that I enter and adds 28 days to it to give me a date 4 weeks hence.

The 4 Week expression field has the format the same as the Start Date: dd/mmm/yyyy

I then created another query that wants to see all dates from the 4 Week expression field that are Between [Enter Beginning of Month] and [Enter End of Month]. I have used a parameter like this frequently and usually am able to enter the information when prompted as something like: March 1, 2004
March 31, 2004

In this query I can only enter the parameter information as:

1 mar, 04
31 mar, 04

None of the conventional ways of entering dates work. Only this one.

Any ideas?

Thanks
irishhelp

 

[batteam]

Does the start date format in the Referral and 4 Week tables have to be in dd/mmm/yyyy format? This is probably why you must enter: 1 mar, 04 for example, in your query parameter box for the 4 Week expression. Try making the formats different in your tables then see if you are allowed to enter a move convention format, like 03/01/04. If you can, you have the source of your problem.

Also, maybe you can change the format for that query parameter box in the query column that you enter the date in by using the properties box that you can choose when you right click your mouse on it. Hope this helps.

 

[lespaul]

I'm surprised this works:

4 Week: [Referral Table]![Start Date]+28

Can you really just add a number to a date and have it calculate correctly?

I would have thought to get a valid "incremented" date you would have to use:

DateAdd('d', [Start Date], 28)

Leslie

 

[irishhelp]

batteam
Do I have to enter the information that way? No. That is how the end users want the information. Oddly enough, the start date has the same format but if I add the same parameter to the start date, I can enter it Jan 1, 04 to Jan 31, 04 but the 4 week date must be entered 1 jan, 04 to 31 jan, 04.


lespaul
Yes this format works. May 21, 2004 + 28 = June 18, 2004 and that is what shows up in my field.

irishhelp

 

[lespaul]

your problem may come from just adding the days to the date instead of using the DateAdd function. I bet that when you do:


May 21, 2004 + 28 = June 18, 2004

it is turning it into a string and it's not a date anymore.

What happens if you use the DateAdd function. Do you still have the same problem?

Leslie

 

[irishhelp]

Leslie
Unfortunately, if I use a Between(date) and (date) it tells me that it is too difficult to evaluate. If I try >[enter beginning of period] I will only get results if I type for example, 01 jan, 04. 1-jan-04 will not work, nor will January 1, 2004 but 1/jan/04 will work. It must be in the format although I use that format all the time and am only having trouble on the calculated field.

Wendy

 

[lespaul]

But what happens if you use the DateAdd function?

Leslie

 

[irishhelp]

I did use the DateAdd function

 

[lespaul]

I'm confused. Could you post ALL the SQL that's involved?

Leslie

 

[irishhelp]

Leslie
This is the SQL for the query I'm trying to run.

SELECT [Q-Participant Entry].[Last Name], [Q-Participant Entry].[First Name], [Q-Referrals].[4 Week], [Q-Referrals].[Exit Date]
FROM [Q-Participant Entry] LEFT JOIN [Q-Referrals] ON [Q-Participant Entry].[S I N] = [Q-Referrals].SIN
WHERE ((([Q-Referrals].[4 Week]) Between [start] And [end]));

 

[lespaul]

and are Q-Participant Entry and Q-Referrals from queries too? What is the SQL for those queries?

 

[irishhelp]

Yes, they are both queries

SQL for Q-Participant Entry:

SELECT [Participant Entry Table].*, ([Participant Entry Table]![InitialEntryDate]-[Participant Entry Table]![Birth Date])/365 AS [Age at Initial Entry]
FROM [Participant Entry Table]
ORDER BY ([Participant Entry Table]![InitialEntryDate]-[Participant Entry Table]![Birth Date])/365 DESC;


SQL for Q-Referral
SELECT [Referral Table].*, DateAdd('d',28,[Start Date]) AS [4 Week], [Referral Table]![Start Date]+56 AS [8 Week], [Referral Table]![Exit Date]+90 AS [3 Month Follow-up]
FROM [Referral Table];

I haven't changed the 8 week or the 3 month follow-up. The same thing with the date parameters happen with those fields.

Wendy

 

[lespaul]

Is there a particular reason you are doing this in multiple queries instead of just one?

SELECT 
[Participant Entry Table].[Last Name], 
[Participant Entry Table].[First Name], 
([Participant Entry Table]![InitialEntryDate]-[Participant Entry Table]![Birth Date])/365 AS [Age at Initial Entry], 
DateAdd('d',28,[Start Date]) AS [4 Week], 
[Referral Table].[Exit Date]
FROM [Participant Entry Table] 
LEFT JOIN [Referral Table] ON [Participant Entry Table].[S I N] = [Referral Table].SIN

WHERE DateAdd('d',28,[Start Date]) Between [start] And [end];

Leslie

 

[irishhelp]

I think for the 4 Week Query, I don't need to use the Participant Query. I could use the Participant Table and the Q-Referral

The Q-Referral is used for a form that is a sub-form of the Participant Form (based on Participant Query).

The 4 Week Query is used to query dates that fall between a range for a report.

I've changed the format so it is mmm-dd-yyyy. I figured out I can still get the information if I type in 01/02/2004 to 15/02/2004. I cannot get it if I type in 01-02-2004 to 15-02-2004 or February 1, 2004 to February 15, 2004.

I think I'll just tell the user to be careful when entering the dates.

Thanks for your help.