User defined date in queries

[Benson112]

Hello all, im new here so hi to everyone.

Im stuck! I am pretty much a novice when it comes to access and have been given the task or creating some reports to stop us doing it manually.

Within a query, I have a date column which we would currently enter the following into the criteria:

Between #01/01/2008# And #31/01/2008#

for the month of January.

What i want to be able to do (and i know is possible as ive done it in the past but cant remember how) is have a pop up when the query is ran to enter the Start date and End date. I remember it being something to do with square brackets but cant get it to work.

Help please! lol

 

[Darth Vodka]

try

Between [EnterStartDate] And [EnterFinishDate]

 

[KenHigg]

Between [Start Date] And [End Date]

 

[boblarson]

In the criteria you would use

Between [Enter Start Date] And [Enter End Date]

but I would suggest using an input form instead. Entering the dates with parameter prompts can be frustrating to your users, especially if they type something wrong. Then they'll have to do it all over again, etc. If you use a form for input, you can eliminate that problem and also you can add a calendar to pick the date from to make it easier for them as well.

 

[KenHigg]

Darth was a tad quicker than I on that one

 

[Benson112]

Thanks all!!

 

[Benson112]

It doesnt work!

When i type in:

Between #01/01/2008# And #31/01/2008#

I get the results that i want, i.e Incidents between those two dates.

When i type in:

Between [Enter Start Date] And [Enter End Date]

I get the pop up boxes, i enter my dates, and it returns results from:

01/01/2008
01/02/2007
01/02/2006

????

 

[Benson112]

In the criteria you would use

Between [Enter Start Date] And [Enter End Date]

but I would suggest using an input form instead. Entering the dates with parameter prompts can be frustrating to your users, especially if they type something wrong. Then they'll have to do it all over again, etc. If you use a form for input, you can eliminate that problem and also you can add a calendar to pick the date from to make it easier for them as well.

Sorry I only just read this, input forms sounds a little out of my league mate, i wouldnt know where to start!

 

[boblarson]

Check out my sample on my website:

http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

 

[Benson112]

Thanks for that Bob ill check it out now,

Does anybody have any clue why im getting the strange results? Access is querying an Oracle database if that makes any difference.

Ive tried using american date formate but still get random results

 

[boblarson]

Are you sure you have actual date fields or are they really text?

 

[Benson112]

Yeah they are definately date fields

Its just strange how if i put in the actual datse i.e. Between #01/01/2008# And #31/01/2008# it works fine, but if i use the promts it doesnt?

 

[Benson112]

Check out my sample on my website:

http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

When i try to open this i get the follownig error message:

Microsoft Access cannot open this file.

This file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems.

To open the file, copy it to your machine or an accessible network location.


I have saved the file to my desktop so not sure what to do now?

 

[boblarson]

When i try to open this i get the follownig error message:

Microsoft Access cannot open this file.

This file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems.

To open the file, copy it to your machine or an accessible network location.


I have saved the file to my desktop so not sure what to do now?

Are you using Access 2007 and have you set your desktop to be a trusted location?

http://www.btabdevelopment.com/main...owtoenablecodeandmacros/tabid/57/Default.aspx

.

 

[Benson112]

I managed to do it, there was an onption in properties to unblock the file