Using DateValue in query (1 Viewer)

[Angel69]

Hi,

How do I use the DateValue function with a query that pulls data between dates? Here is my code below. I'm getting an error that my expression is too complex or incorrect.

SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, Count(tblPACSTemplate.temID) AS CountOftemID, tblPACSTemplate.Type, DateValue([ApprovedDate]) AS ApproveDate
FROM tblPACSTemplate LEFT JOIN tblAUCodes ON tblPACSTemplate.[OpenByAU] = tblAUCodes.AU
GROUP BY IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))), tblPACSTemplate.Type, DateValue([ApprovedDate])
HAVING (((DateValue([ApprovedDate])) Between [Forms]![Main Menu]![StartDate] And [Forms]![Main Menu]![EndDate]))
ORDER BY IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS"))));

TIA

 

[vbaInet]

It's a real Date/Time field right? You don't need DateValue().
And it should be in the WHERE clause not HAVING.

 

[Angel69]

I need the DateValue because when I select the date range for exampel 6/22/14 - 6/30/14 none of my 6/30/14 items show up I'm guessing because the time is later in the day. I use the DateValue to strip the time.

 

[vbaInet]

Alright that's fine.

 

[Angel69]

I'm getting the attached error when I change it to WHERE instead of HAVING.

 

[vbaInet]

Create a query based on this one and add the WHERE criteria there.

 

[vbaInet]

I'll expand on my last post.

Save this query (as qryTest for example):

SELECT IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",
               IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",
                   IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",
                       IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))) AS LOB, 
          Count(tblPACSTemplate.temID) AS CountOftemID, 
          tblPACSTemplate.Type, 
          DateValue([ApprovedDate]) AS ApproveDate
FROM tblPACSTemplate LEFT JOIN tblAUCodes ON tblPACSTemplate.[OpenByAU] = tblAUCodes.AU
GROUP BY IIf([tblAUCodes].[BusinessLine] Like '*CMES*',"CMES",
                    IIf([tblAUCodes].[BusinessLine] Like '*HOUS*',"CMES",
                        IIf([tblAUCodes].[BusinessLine] Like 'CTO',"CTO",
                            IIf([tblAUCodes].[BusinessLine] Like 'Shareowner*',"WFSS","SPS")))), 
                tblPACSTemplate.Type, 
                DateValue([ApprovedDate]);

Create a query based on qryTest and add the sort/criteria here:

WHERE [ApproveDate] Between [Forms]![Main Menu]![StartDate] And [Forms]![Main Menu]![EndDate]
ORDER BY LOB

 

[Angel69]

Thanks for taking a look at it but that does not seem to work either. I'm getting a Data type mismatch in criteria expression error.

 

[vbaInet]

Where are you getting the data type mismatch error? Which query?