Please please please

[dazedandconfused]

Sorry guys, last question and I´m out of your hair forever. My personal deadline for getting this finished is today so if someone could please please help me answer this quick query...

I have written a macro that pulls up a query with all the information I need and automatically exports it to Excel. However, I am having trouble with applying a date range filter.

When my users click on ´monthly report´ they have to enter the date range for the report. They then click on a button that runs the macro...etc. However, I don´t know how or what statement/command to put into the Macro that applies this filter. If anyone can give me this and the exact command/text to type that would be great.

HELP! PLEASE! I´LL BUY YOU A BEER!

 

[neileg]

You need to apply the criteria in the query, not in the macro.

 

[dazedandconfused]

HOW?

Pretty please

 

[tehNellie]

Basic example:

SELECT date
FROM myTable
[b]WHERE date > [enter start Date] AND date < [enter End Date][/b]

The square brackets prompt the user for a value with the contents as the Prompt.

 

[dazedandconfused]

Basic example:

SELECT date
FROM myTable
[b]WHERE date > [enter start Date] AND date < [enter End Date][/b]

The square brackets prompt the user for a value with the contents as the Prompt.

Thank you.

So where exactly do I copy and paste what you´ve written into the query sheet in design view?

 

[tehNellie]

switch to SQL view or add

>[enter startdate] And <[enter end date]

to the date column in design view

 

[dazedandconfused]

Thank you. Let´s try...

 

[dazedandconfused]

Great. That seems to work, except...does it calculate the dates on when the records were entered into the database?

I´m trying to link these dates to a field called ´Date of contact´so we can sort them by this.

 

[tehNellie]

Depends on what the dates in your column refer to.

Going by the name I'd guess the answer is no, it's filtering based on the date of contact.

the WHERE clause isn't doing any calculation per se, it's just filtering your results based on the criteria you're entering.

 

[dazedandconfused]

Ok, I´ve checked the SQL and it is using ´date of contact´for this criteria.

why is it not displaying records that have this field completed and fall between these dates?!?!

 

[dazedandconfused]

Depends on what the dates in your column refer to.

Going by the name I'd guess the answer is no, it's filtering based on the date of contact.

the WHERE clause isn't doing any calculation per se, it's just filtering your results based on the criteria you're entering.

the dates in the colum refer to date of contact.

 

[dazedandconfused]

Anyone?

Can anyone tell me why it won´t display certain fields that I know fall within the date range?

 

[tehNellie]

why is it not displaying records that have this field completed and fall between these dates?!?!

Your column is a date field and not text?

 

[dazedandconfused]

Yes it is. Do I have to change it to a text field?

 

[dazedandconfused]

and if so, how will this affect the data already in there?

 

[tehNellie]

no, it should be a date field.

When you say it doesn't show certain fields does that mean it does show some information?

 

[dazedandconfused]

It only pulls out some of the records. Going by the month of Feb, we had 113 cases. When I searched by date, it only pulled out 89 of them.

 

[neileg]

You're not giving us enough information to be able to help you. Date matching can be tricky. For example, if your date field holds date and time and you supply a date as a criterion, the time portion can cause the data to be excluded. If the data looks like 28/02/2008 but actually is 28/02/2008 12:30:00 and your criterion is 20/02/2008, the time portion defaults to 00:00:00 so your data is after the criterion you supplied.

Give us some sample data.

 

[dazedandconfused]

switch to SQL view or add

>[enter startdate] And <[enter end date]

to the date column in design view

Thanks for the help on this.

I´ve realised why the records weren´t showing. Basically because I had linked tables and they wouldn´t display the ones with null values.

However I have another problem...I´ve entered the code as above and it doesn´t seem to have any effect on the dates of the records I pull out. Basically, the end date seems to work but it ignores the start date.

anyone any ideas?

 

[thisisntwally]

its hard without seeing your code.
Does it look like its pulling out entries where date > startdate AND entries where date < enddate?
Or is it just pulling out entries where date < enddate regardless of its relationship with startdate?

was that a clear question?