Problem constraining query on alias 1

[jpl458]

I need to constrain a query based on the alias that I created for a column. I have one that works and another that doesn't.

The following works:
RDate: [PUB_PSHISTOR]![PSH-Date] is in the line of the QBE Grid

#7/17/2014# is in the criteria line for the above and it works fine.

neither of the following work following does not work
RCode: [PUB_PSHISTOR]![PSH-TC] in the field line

Between 1001 and 1100

Between "1001" and "1100"

But if I just put the PSH-TC in the criteria field and put between 1001 and 1100
ACCESS puts the quotes in and it works fine.

Thanks for the help in advance

jpl

 

[SkipVought]

Hi,

Please post the entire SQL.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[jpl458]

Here is the complete SQl.

SELECT [PUB_PSHISTOR]![PSH-Date] AS RDate, Format([PUB_PSHISTOR]![PSH-Time]/86400,"hh:nn:ss ampm") AS RTime, "Fee" AS RndType, PUB_PSHISTOR.[PSH-TC], CStr([PUB_PSHISTOR]![PSH-TC]) AS RCode, [PUB_PSCODES]![PSC-PrtDesc] AS RCDescription, " " AS TnNumb, [PUB_PSHISTOR]![PSH-RcptNumb] AS RReceipt, [PUB_PSHISTOR]![PSH-FeeCnt] AS RCount, Format([PUB_PSHISTOR]![PSH-Time]/86400,"hh") AS RHour, IIf(Month([PSH-Date])<5,Year([PSH-Date]),Year([PSH-Date])+1) AS FYr, Format([PUB_PSHISTOR]![PSH-Date],"yyyy") AS CalYr, IIf(Month([PUB_PSHISTOR]![PSH-Date])>=5 And Month([PUB_PSHISTOR]![PSH-Date])<=12,Month([PUB_PSHISTOR]![PSH-Date])-4,Month([PUB_PSHISTOR]![PSH-Date])+8) AS FMo, Format([PUB_PSHISTOR]![PSH-Date],"mmm") AS CalMo, Format([PUB_PSHISTOR]![PSH-Date],"mmm") & " " & Format([PUB_PSHISTOR]![PSH-Date],"ww") AS WeekNum, Weekday([PUB_PSHISTOR]![PSH-Date],2) AS WkDay, Format([PUB_PSHISTOR]![PSH-Date],"ddd",2) AS DayNme, Day([PUB_PSHISTOR]![PSH-Date]) AS MoDay
FROM PUB_PSCODES INNER JOIN PUB_PSHISTOR ON PUB_PSCODES.[PSC-TC] = PUB_PSHISTOR.[PSH-TC]
WHERE ((([PUB_PSHISTOR]![PSH-Date])=#7/17/2014#) AND ((CStr([PUB_PSHISTOR]![PSH-TC])) Between 1001 And 1100));

Got this to work last night:
RDate: [PUB_PSHISTOR]![PSH-Date] was the original non-working version, but
RDate: CStr([PUB_PSHISTOR]![PSH-Date]) works, but takes a long time to process

If there is a better way let me know.

Thanks

jpl

 

[SkipVought]

RDate: CStr([PUB_PSHISTOR]![PSH-Date])

Why do you want to convert your date value to a string?

 

[jpl458]

I was confused, and was cross-talking two fields. It was just pilot error. Sorry for taking your time.

Thanks

jpl