Date manipulation code help

[bgraybea]

I'm new to the forums and am hoping someone can help. I have date data stored in a table as 8 digits YYYYMMDD. I basically need to group data by month using a mid-month cutoff - for example, everything between 20101016 and 20101115 would be grouped as 201011. I've written the following as a new field in my query, but I get the error: "Syntax error (comma) in query expression" when I try to run the query. Any help would be greatly appreciated - either corrections to this code or better approaches. Thanks!

YYYYMM:
IIF(
Right([Liquidation Data 201006 - 201101].[IDENTRDT],2)>15
AND
Mid([Liquidation Data 201006 - 201101].[IDENTRDT],5,2)+1=13,
Left([Liquidation Data 201006 - 201101].[IDENTRDT],4)+1,
Left([Liquidation Data 201006 - 201101].[IDENTRDT],4)
)
&
IIf(
Right([Liquidation Data 201006 - 201101].[IDENTRDT],2)>15,
(iif
(Mid([Liquidation Data 201006 - 201101].[IDENTRDT],5,2)+1=13,
”01”,
Format(Mid([Liquidation Data 201006 - 201101].[IDENTRDT],5,2)+1,"00")
),
Mid([Liquidation Data 201006 - 201101].[IDENTRDT],5,2))
)

 

[vbaInet]

A quick question. Is there any reason why you don't want to store the date as a real date, i.e. in the format dd/mm/yyyy

 

[bgraybea]

To answer your question, I'm querying a data table that someone else provides, so the date is coming to me in a YYYYMMDD number format.

I did figure out how to get the answer I was after, and it was much simpler than I thought after sort of going a different route...here's the successful code:

YYYYMM:
IIF(
Right([Liquidation Data 201006 - 201101].[IDENTRDT],2)>15,
IIF(
Mid([Liquidation Data 201006 - 201101].[IDENTRDT],5,2)=12,
Left([Liquidation Data 201006 - 201101].[IDENTRDT],4)+1 & 0 & 1,
Left([Liquidation Data 201006 - 201101].[IDENTRDT],6)+1
),
Left([Liquidation Data 201006 - 201101].[IDENTRDT],6)
)

 

[vbaInet]

Excellent! Glad to see you got it working.