Parameter Date Query 1

[shirlthewhirl]

Hi,
I have a reservations Db with Tour Dates for each Customer.
I needed to set a reminder date 12 days before Tour. This I have done - works fine. I set the 12 days before as an expression in the query. Now all I want to do is set a parameter to enter current days date - just to show the actual customers with tours 12 days from the current date.
I cannot do this !! Please help.
Shirley

 

[scriverb]

Here is some SQL that should work for you. The column TourMinus12 takes the table field Tour and subtracts 12 days from it. The Where statement selects records where the expression Tour - 12 days equals todays date.

Select A.*, (A.Tour -12) as TourMinus12
FROM tblYourTablename as A
WHERE (A.Tour-12) = Date();

Please post back with comments or questions. If you still have problems or I am not understanding please post your already created SQL from your query so that I may take a look at it.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[shirlthewhirl]

Thank you very much for trying to help me. I think I may not have explained myself very well:- Below part of Query
pulled from tbldateandbusdetails
Field named Expr1ateAdd("d",-12,[TourDate])

TourDate Final Reminder
12/08/2003 31/07/2003
01/08/2003 20/07/2003
01/08/2003 20/07/2003
25/07/2003 13/07/2003
25/07/2003 13/07/2003
20/07/2003 08/07/2003
20/07/2003 08/07/2003

I want a parameter box that allows you to enter the current date and for instance it would just give all details for final reminder date of 13/07/03 for Tours happening on 25/07/03. This to allow a reminder to be sent for final payment for Tour to be made.
Hope I have now explained a bit better.
Shirley

 

[scriverb]

That's how I understood it. I just was using a shorter version of your DateAdd expression by using ([TourDate] - 12) which gives you the same thing. I have updated the query with your table and field names and used the DateAdd expression. This should select the records with a TourDate-12 that match todays date:

Select A.*, DateAdd("d",-12,[TourDate]) as Expr1
FROM tbldateandbusdetails as A
WHERE DateAdd("d",-12,[TourDate]) = Date();

Are you sure you want a parameter prompt for the User to enter todays date?, over just using the Date() function which is todays date. If you want the parameter prompt then use the following:

Select A.*, DateAdd("d",-12,[TourDate]) as Expr1
FROM tbldateandbusdetails as A
WHERE DateAdd("d",-12,[TourDate]) = [Enter Todays Date(dd/mm/yyyy): ];

Post back if you have any questions.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[shirlthewhirl]

Thank you very much for your help with my problem - unfortunately I still have it. I have tried absolutely everything with the answer you sent me, in every combination I can think off. However, it just will not work. It keeps asking me for further brackets or [] or () all of which I have tried. The closest I seem to have got is a message which reads as follows:- "You have written a subquery that can return more than 1 field without using the EXISTS reserved word in the main queries FROM clause. Revise the SELECT statement of the subquery to request only one field".
I feel so thick because after your help I thought I could sort it.
Anymore help you could give me would be greatly appreciated. This has caused me more trouble than all the rest of the database put together.
Best wishes,
Shirley

 

[scriverb]

Let's try this modification:

Select A.*, DateAdd("d",-12,[TourDate]) as Expr1
FROM tbldateandbusdetails as A
WHERE [Enter Todays Date(dd/mm/yyyy): ] = DateAdd("d",-12,[TourDate]);

Maybe ACCESS didn't like the prompt on the right side of the expression where you usually put a subquery if there is one. Since there isn't one then it is probably interpreting the [Enter Todays Date(dd/mm/yyyy): ] as a subquery of sorts.

Also, did you try running the other example which uses Date() rather than prompts. It is identication to this parameter drive query but it uses Todays date also. No User intervention necessary.

Post back with your results.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[shirlthewhirl]

Hi Bob,
Yes I did try both solutions you gave me. However, because I felt so ashamed that I was bothering you with such a small issue, I have kept going and I cannot belief that with what I had in the beginning and putting what you suggested as Date(), where I had kept putting Now() in Criteria line and doing the same thing in the pull down parameter box - it works perfect.
I am so grateful for your help as it has kept me going with sheer determination to sort it out.
With grateful thanks,
Shirley

 

[scriverb]

I am pleased that you solved your problem and never think that you are bothering any of us here at TT with trivial problems. When there's a problem it is always big if you can't solve it.

Now the difference between Date() and Now() is something you definately should understand. Rather than me rambling on here I recently responded to a thread which progressed into a really good explanation of the Date() and Now() functions and their uses. It also explained why you have to be careful with their uses in storing and then selecting the values. If you mix them up you won't get accurate query results. There is also a link in this thread that goes into great depth on this subject. My advise is to read the thread and the link carefully and even copy the link for further reference.

thread705-601121

Good luck with your project. Thanks for the star as it is appreciated.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[shirlthewhirl]

Hi Bob,

Thanks for pointing me in the direction of the date and time issues - and to think that I thought that these would be the most straight forward things I would have to deal with.

Live and learn - she said to herself.

Thanks again.
Shirley