Date to Date query

[Pusher]

Hi all,
What code is used to get 2 dates from a form to the query criteria box so i can chose what start and what end date is.

Thanks

 

[CBrighton]

Use this as criteria:

Between [forms]![FormName]![StartControlName] And [forms]![FormName]![EndControlName]

Obviously alter the form & control names to suit your form.

Alternatively this will give a popup input box asking for the date (which may be better for some situations:

Between [Enter start date] And [Enter end date]

Unless you have fields with the names "Enter start date" and "Enter end date", in which case it will use those as the dates.

:edit:

Right clicking the top part of the macro is design mode (the grey bit where the tables fo) and clicking Paramaters will let you add Enter start date & Enter end date as paramaters with specific data types (e.g. date/time)

 

[Pusher]

How to make the query be if left blank all the time or if the first time is entered then from that time until now?

When i enter this code

Between [forms]![FrmPrikazIzvestaja]![Text35] And [forms]![FrmPrikazIzvestaja]![Text37]

He only list EndControlName lines in the table- just at that date and not all the dates in between

 

[shutzy]

you can use the < and > symbols. say 1st quarter this year = > 01/01/2011, <31/03/2011. i think. give it a try and let me know.

i learnt this from a book called

access 2010 - the missing manual. its a 800+ page book that has helped me loads and im only upto page 300ish

 

[MSAccessRookie]

How to make the query be if left blank all the time or if the first time is entered then from that time until now?

When i enter this code

He only list EndControlName lines in the table- just at that date and not all the dates in between

Between is the way to go here. If you want control over the values to compare, try using the IIf() function instead of the date value.

IIf(ConditiontoTest, ValueifTrue, ValueifFalse)

You could test the value of the date, substituting any result you want if it fails the test, and using the date if it passes the test.

 

[vbaInet]

For dates BETWEEN is the preferred method but there's no harm trying >= <=

I think your problem is the way the date is formatted. Let's see your database.

 

[Pusher]

Here is my DB - The Report Form is FrmPrikazIzvestaja and the Query for that form is QryIZVESTAJ

 

[vbaInet]

Tell me what to enter in the three controls and what you expect to see.

 

[Pusher]

The first is the number of the terrain the second is the start date and the third is the end date.
From the table IZVESTAJ the interventions are the data I want to see in the query.
If every box is set then the interventions that are between those two dates from that terrain
If the 2 and 3 box is clear ALL the interventions from that terrain
if the 3 is clear then the interventions from start date until now from that terrain

 

[vbaInet]

And if only 2 is clear?

 

[Pusher]

Yes, if only second is clear then from the begging date(1.1.2011) to the date in the 3 box

 

[vbaInet]

Ok. See attached.

Make sure to leave the Alternative textboxes in the Header or Footer section of your form.

 

[Pusher]

Can you explain what did you do?
Thanks

 

[vbaInet]

In two alternative textboxes called txtAltStart and txtAltEnd I put:
DMin() - get the minumum date in your table
DMax() - get the maximum date in your table

Criteria under your date field is something like:

BETWEEN Nz(txtStart, txtAltStart) AND Nz(txtEnd, txtAltEnd)

So if your original textboxes called txtStart and txtEnd are empty (or null), it will use the Min and Max values from the alternative textboxes. Nz() lets you give an alternative value if it is Null.

 

[Pusher]

Thanks
That did it

 

[vbaInet]

I already did it. It's using DMax() in the Control Source property. Or is that not what you're asking about?

 

[Pusher]

Its not working again... i don't know if i did something – FrmPrikazIzvestaja is the form and QryIZVESTAJ is the Query if you don’t remember.

 

[vbaInet]

I would advise you go back to what you were advised and re-do it all.

 

[Pusher]

I did... copy pasted it all but i get too little results for my query... i have 3000 lines in my table and i get 50 in my query... there is some problem... i can't see it... i will try again.

 

[vbaInet]

Remember that the names I used for the controls are important and most importantly your controls should not be the same name as their bound fields.