Perticular search between 2 dates in 2 tables

[Guest]

I have 2 Tables "Purchase" & "Sales"
am having a problem with one of the querries when i want a balance of
Purchase & Sales for a perticular month.

As when i run a querry it first ask me Purchase Date Period (DDMMYY upto
DDMMYY) Then Sales Date Period (DDMMYY upto DDMMYY).

Is it possible i hv to input only the month one time and it automatically
checks from 1st Date of the month upto the end of the month.

If its possible please give an example how to do it.

Thanks

 

[Allen Browne]

To enter the dates once only, create a form with two unbound text boxes,
named (say) txtStartDate and txtEndDate. Save the form as (say) frmGetDates.

In the Criteria row of your query, under the PurchaseDate field, enter:
Between [Forms].[frmGetDates].[txtStartDate] And
[Forms].[frmGetDates].[txtEndDate]

Do the same in the other query. The queries will both read the dates from
the form.

To get this to work reliably with your dd/mm/yy date format:
1. Set the Format property of the text boxes to "Short Date" or similar.
Even though they are unbound, Access now knows how to interpret them, and
will not accept invalid dates.

2. Declare the 2 paramters in each query. In query design view, choose
Parameters on the Query menu. Enter two lines like this:
[Forms].[frmGetDates].[txtStartDate] Date/Time
[Forms].[frmGetDates].[txtEndDate] Date/Time

More info on handling dates:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

 

[google]

you could use the format- function to create a string with the
particular year/month:

SELECT * FROM Purchase, Sales
WHERE format(purchase_Date,"mmyyyy") < [periodParameter]
and format(sales_Date,"mmyyyy") < [periodParameter]

then you'll have to fill in you parameter (call PeriodParameter) with a
value like '032005' for march 2005.

Good Luck

 

[google]

Excuse me for my previous post .... Of course it should be

WHERE format(purchase_Date,"mmyyyy") = [periodParameter]

(EQUALS in stead of smaller than)