modify query to show only todays results

[antonyx]

hi, i have certain jobs and i want to select them based on the job date.

in my current query, i use the job date parameter
[Enter a Date:]

when the user types in the date, then the jobs for that date and their corresponding information is displayed.

however, i have created a form for this query displaying the results as a datasheet.

instead of the user having to enter the date for the query manually, is there any way to modify the query to only show results for the current day.

eg. today is the 24/02/06.. so i would like the results from the query to automatically show the jobs for that date. this way, only todays jobs will be shown in my form.

 

[TheAceMan1]

How are ya antonyx . . .

Remove the parameter for the date and in the criteria enter:

[blue]Forms![purple][b]YourFormName[/b][/purple]![purple][b]TextboxName[/b][/purple] OR Forms![purple][b]YourFormName[/b][/purple]![purple][b]TextboxName[/b][/purple] IS Null[/blue]

See Ya! . . . . . .

 

[antonyx]

i dont actually have a text box that has todays that tho.. shall i make one and then link the query to that text box??

 

[stix4t2]

Antonyx, how are you doing? You just keep plugging away don't you. Boy I wish I had this kind of deal when I started developing.

First using a parameter in a query is not much of a solution for the users. First it hard to validate entry and second the entry form pops up out of the blue. That said, if you replace the [Enter a Date:] with the Date() function it will work for just the current date.

But here's a little lesson in list review. I had talked about continuos forms before, this is a setting in the forms properties. If you are not familiar, the form is usually very short, but it repeats for each record in the record source. It is kind of like a dataset, and showing the form in dataset works as well. If you drag that form on to a new form, parent. This is how you make a sub form. These two forms create a parent child relationship.

We do this so we can put filters on the parent form, and then requery the child form to limit the list. So in the case you described, you could create an unbound date field at the top and then use the field to filter the continuous form. This opens the door to better control over what they are seeing in the list. You can offer more ways to limit the list than just date. Also as you work on it, you can also fire of your entry form by selecting on a listing.

Pretty cool stuff.

 

[antonyx]

Antonyx, how are you doing? You just keep plugging away don't you.

i couldnt help but chuckle on reading this. i know i am a bit persistent.. maybe a bit of a lunatic you could say.

i am very thankful that you are giving your time and knowledge to me.

when i am more access profficient, im sure i will be doing things the 'best' way and 'most flexible' way so on. i have to balance what i know with what i am trying to do as you will understand, otherwise i would be posting twice as many threads. also this database is not a contracted job, it is one i am doing for a good friend in return for a part in his business in the future. i can safely say i wouldnt get anywhere without these forums.

 

[stix4t2]

Give a man a fish he eats for a day. Teach a man to fish he eats for life. And do on to other as you would like others to do on to you. You are a good friend to put your sanity on the line for a friend, I hope he appreciates your efforts.

I am the first to get up and ask questions if I don't know. Its better to get some help then bang your head against the computer. Give it the old college try, but this site has so many good resources, it speeds up your learning, plus it reduces bad habits, like putting parameters in queries, lol.

 

[antonyx]

lol, that was a cheap shot.

believe me, ill answer any questions i can in the future, and continue to feed this page.

and yes my friend worships me.. im actually a website man, im making 2 websites for him aswell believe it or not.

my only motivation is that his cab company operates through heathrow, and heathrow has 60 million people travelling through it each year.

his business needs this electrical upgrade in every sense.

 

[TheAceMan1]

antonyx . . .

So sorry . . .

Remove the parameter for the date and in the criteria enter:

[blue]Int(Now())[/blue]

See Ya! . . . . . .

 

[antonyx]

ok i have done that.. can i just ask why.. why would date() not be suitable as the criteria??

 

[TheAceMan1]

antonyx . . .

No special reason other than a programming habit of mine. Comes from designing enough db's where full Date & Time are required, which is what [purple]Now()[/purple] returns.

See Ya! . . . . . .

 

[antonyx]

hi, just to follow up on this question.. this in my parameter allows me to view todays jobs

Int(Now())

is it possible to view todays and tommorows jobs by altering this coding???

 

[PHV]

Between Date() AND 1+Date()

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[antonyx]

ok last post on this query, thanks again for the help.

i have a Yes/No checkbox to indicate whether the job has been done.. if it is done then i want it removed from the list.. so i think my code needs to be something like..

WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) AND Booking_Confirm.Job_Done ="No"

or something like this??

 

[antonyx]

here is the sql for my query at the moment

WHERE (((Booking_Main.Job_Date) Between Date() And 1+Date())) And Booking_Confirm.Job_Done=False
ORDER BY Booking_Main.Job_Date, Booking_Main.Job_Time;

when i execute the query a parameter box comes up displaying Booking_Confirm.Job_Done

i type false and still no records appear.. this box should not come up tho in the first place.. is the statement above missing something??

 

[stix4t2]

Have you rechecked you table to confirm the name because you are right it should not come up. If you use a " " in then ame like "job done", remember to put it in square brackets [job done]. Also if you provide the select statement we can confirm the Booking_Confirm table in the query.

 

[antonyx]

yes, my apologies.. i hadnt included the confirm details in the query, thanks a lot, it works.