Date Problem

[jboomer]

I am fairly new but i've been poking around quite a bit. I have a very simple database with 3 tables. I have one Primary table that has one to one relationships with feild in the other tables.

I want to run a query that will show just the recodrs from a particular month. i formated the date feild as a short date. I created the query for a report.

I want the Prompt to ask for an Invoice date that will be the last day of the month. and i want it to return only the records for the past month.

I added a feild to the query: Month([InvoiceDate]) and the criteria is [choose the month].

I also added another field Year([InvoiceDate]) simlar criteria.

when i run the query though it returns data from other months besides the the month i put in.

Need help. Thanks

 

[KenHigg]

Not to be avoiding the problem but why not just prompt the user for a start date and an end date? They already have to respond to two prompts -

 

[jboomer]

Tried that

I tried to do the Betwenn range. what i did was put in
Between [Start Date] And [End Date]

it seemed to work but it put in multiple dupliclates.

 

[jboomer]

Duplicates

Anyone know why there is duplicates. I am at a loss.

 

[raskew]

Hi - Without seeing your code, can't provide a precise solution. However, since you're trying to return records from a specific month/year, you should be able to accomplish it with just one prompt in your criteria statement, e.g.

format([InvoiceDate], "mm/yyyy") = [enter mm/yyyy]

Bob

 

[jboomer]

Still not working

The Query returned not just the month but everything. from all months. Any ideas. If i use the process it shows everything. and if i use the between command it shows duplicates not sure what to do?

 

[raskew]

Hi -

Open the underlying table in design-view. What is data-type (not format) of your date field. It should be date/time. If, instead, it's text -- there lies the problem.

Bob

 

[jboomer]

situation

It is a date and time type with a short dat format. I would be glad to send the database for review it is probally a very easy problem to fix. It is Access 2007 if that makes any difference.