query using form for date range

[the bp Guy]

I am using a form to supply date for report query. I want to use the same
date in three different places. as you see in the SQL the three places for
dates. when I add +5 to the end I get error that its to complex. I take it
off works ok. I can run it without the form using between #01/01/01# and
#01/01/01#+5 and it works
SELECT tblInvoice.Date, tblInvoice.Customer, tblInvoice.[Invoice#],
tblInvoice.Amount, tblInvoice.creditinvoice, tblcreditcard.Gross,
tblcreditcard.Fee, tblcreditcard.Date
FROM tblInvoice LEFT JOIN tblcreditcard ON tblInvoice.Customer =
tblcreditcard.Customer
WHERE (((tblInvoice.Date)=[Forms]![frmstuartreport]![Text0]) AND
((tblInvoice.Customer)="stuart") AND ((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And [Forms]![frmstuartreport]![Text0]+5))
ORDER BY tblInvoice.Date, tblcreditcard.Date;
Thanks for your help

 

[KARL DEWEY]

Try this --
((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And
CVDate([Forms]![frmstuartreport]![Text0])+5))

 

[the bp Guy]

works great Jus curious what cvdate does

Try this --
((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And
CVDate([Forms]![frmstuartreport]![Text0])+5))

--
KARL DEWEY
Build a little - Test a little

I am using a form to supply date for report query. I want to use the same
date in three different places. as you see in the SQL the three places for
dates. when I add +5 to the end I get error that its to complex. I take it
off works ok. I can run it without the form using between #01/01/01# and
#01/01/01#+5 and it works
SELECT tblInvoice.Date, tblInvoice.Customer, tblInvoice.[Invoice#],
tblInvoice.Amount, tblInvoice.creditinvoice, tblcreditcard.Gross,
tblcreditcard.Fee, tblcreditcard.Date
FROM tblInvoice LEFT JOIN tblcreditcard ON tblInvoice.Customer =
tblcreditcard.Customer
WHERE (((tblInvoice.Date)=[Forms]![frmstuartreport]![Text0]) AND
((tblInvoice.Customer)="stuart") AND ((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And [Forms]![frmstuartreport]![Text0]+5))
ORDER BY tblInvoice.Date, tblcreditcard.Date;
Thanks for your help

 

[KARL DEWEY]

I think of it as Con_Vert_Date

Access interpets your first [Forms]![frmstuartreport]![Text0] string as a
date because it fits the date format and is evaluating a date.

The second is concatenating 5 to the string to appear as '8/28/20085' and
does not increment the date by 5.

--
KARL DEWEY
Build a little - Test a little

works great Jus curious what cvdate does

Try this --
((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And
CVDate([Forms]![frmstuartreport]![Text0])+5))

--
KARL DEWEY
Build a little - Test a little

I am using a form to supply date for report query. I want to use the same
date in three different places. as you see in the SQL the three places for
dates. when I add +5 to the end I get error that its to complex. I take it
off works ok. I can run it without the form using between #01/01/01# and
#01/01/01#+5 and it works
SELECT tblInvoice.Date, tblInvoice.Customer, tblInvoice.[Invoice#],
tblInvoice.Amount, tblInvoice.creditinvoice, tblcreditcard.Gross,
tblcreditcard.Fee, tblcreditcard.Date
FROM tblInvoice LEFT JOIN tblcreditcard ON tblInvoice.Customer =
tblcreditcard.Customer
WHERE (((tblInvoice.Date)=[Forms]![frmstuartreport]![Text0]) AND
((tblInvoice.Customer)="stuart") AND ((tblcreditcard.Date) Between
[Forms]![frmstuartreport]![Text0] And [Forms]![frmstuartreport]![Text0]+5))
ORDER BY tblInvoice.Date, tblcreditcard.Date;
Thanks for your help