Open form criteria between dates

[ben1234zz]

Hi

We are trying to open a form showing items from the last 7 days, we have the below VBA criteria:

stLinkCriteria =
"([DateCreated] BETWEEN #" & Format(DateAdd("d", -7, Date), "d/m/yyyy") & _
"# AND #" & Format(Date, "d/m/yyyy") & "#) OR ([InvoiceDate] BETWEEN #" & _
Format(DateAdd("d", -7, Date), "d/m/yyyy") & "# AND #" & Format(Date, "d/m/yyyy") & "#)"

This is the result, but it says there is an error near #

([DateCreated] BETWEEN #27/7/2009# AND #3/8/2009#) OR ([InvoiceDate] BETWEEN #27/7/2009# AND #3/8/2009#)

I cant see the issue.

Thanks
B

 

[dhookom]

These expressions expect the date in format of m/d/yyyy.

stLinkCriteria =   "([DateCreated] BETWEEN #" & Format(DateAdd("d", -7, Date), "m/d/yyyy") & _  
   "# AND #" & Format(Date, "m/d/yyyy") & "#) OR ([InvoiceDate] BETWEEN #" & _  
   Format(DateAdd("d", -7, Date), "m/d/yyyy") & "# AND #" & Format(Date, "m/d/yyyy") & "#)"

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Why not simply this ?

stLinkCriteria = 
  "(DateCreated BETWEEN (Date()-7) And Date()) OR (InvoiceDate BETWEEN (Date()-7) And Date())"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ben1234zz]

Hi

Thank you both,

dhookom i have amended as you say however the error still returns.

PHV i get the error 'Date' is not a recognized built-in function name.

This maybe because we are running an SQL Server backend.

Thanks
B

 

[PHV]

OK, hopefully next time you'll explain the whole story ...

 

[dhookom]

Are both date fields actually dates?
Is this an ADP or MDB?
What expression works if you simply type dates into the criteria?

Duane
Hook'D on Access
MS Access MVP

 

[ben1234zz]

Sorry for not explaining the whole story... And thanks for your posts:

They are SQL Server DATETIME fields.

Its an ADP

Acutally I made it work by using this code:

stLinkCriteria = "([DateCreated] BETWEEN '" & Format(DateAdd("d", -7, Date), "yyyymmdd") & "' AND '" & Format(Date, "yyyymmdd") & "') OR ([InvoiceDate] BETWEEN '" & Format(DateAdd("d", -7, Date), "yyyymmdd") & "' AND '" & Format(Date, "yyyymmdd") & "')"

Thanks again for your input.

B

 

[dhookom]

ADP is a huge factor in providing an answer to your question. PH's solution could be

stLinkCriteria = 
  "(DateCreated BETWEEN (GetDate()-7) And GetDate()) OR (InvoiceDate BETWEEN (GetDate()-7) And GetDate())"

Duane
Hook'D on Access
MS Access MVP