1 Time Query

[Pharaoh]

Hi,

I have written a query using 3 tables all joined by similar properties. I run the query and get my results. However, if i alter any of the selected fields, it corrupts the query and the one of my expressions goes haywire.

If then quit without saving and go back into it it's fine and works but as soon as i attempt to change anything it corrupts it.

One thing i did notice is that one of my IIF statements gets converted from "Group By" to "Expression" when the query is run. However it works still.

I've tried recreating the query and it still only works until it's changed in any way.

Any Ideas?

Regards

Pharaoh

 

[vbaInet]

Have you tried creating your query from scratch?

Maybe there's something with the syntax. Let's see the sql statement of the query.

 

[Pharaoh]

Hi,

I tried re-creating it from scratch a couple of times. It won't let me view the SQL view and i am greeted with this message:

Syntax error (missing operator)

I looked over the IIF statements as that was the ones it seems to think are incorrect but they look ok to me and since they work the one time i would have thought they were ok.

Time Periods: IIf([time] Between #09:00:00# And #11:59:59#,"09:00-11:59",IIf([time] Between #10:00:00# And #11:59:59#,"10:00-11:59",IIf([time] Between #12:00:00# And #14:59:59#,"12:00-14:59",IIf([time] Between #15:00:00# And #17:59:59#,"15:00-17:59",IIf([time] Between #18:00:00# And #20:00:00#,"18:00-20:00","Out Of Hours")))))

That is the IIF i have been using. basically i use it twice; once to show the actual results and once with group and sorted but not shown.

Regards

 

[vbaInet]

I tried re-creating it from scratch a couple of times. It won't let me view the SQL view ...

Remove the IIF() alias field from design view and see if you can get to the sql view.

In any case, I would employ a function for this instead. Copy and paste this into a Module:

Public Function GetTimeSlot(strTime As Variant) As Variant
    If IsDate(strTime) = False Then
        GetTimeSlot = vbNullString
        Exit Function
    End If

    GetTimeSlot = Format(GetTimeSlot, "hh:mm:ss")

    Select Case strTime
        Case #9:00:00 AM# To #11:59:59 AM#
            GetTimeSlot = "09:00-11:59"
        Case #10:00:00 AM# To #11:59:59 AM#
            GetTimeSlot = "10:00-11:59"
        Case #12:00:00 PM# To #2:59:59 PM#
            GetTimeSlot = "12:00-14:59"
        Case #3:00:00 PM# To #5:59:59 PM#
            GetTimeSlot = "15:00-17:59"
        Case #6:00:00 PM# To #8:00:00 PM#
            GetTimeSlot = "18:00-20:00"
        Case Else
            GetTimeSlot = "Out Of Hours"
    End Select
End Function

So now you can just use:

Time Periods: GetTimeSlot([time])

By the way, you shouldn't use TIME as your field name. That's a reserved keyword in Access.

 

[Pharaoh]

Hi,

I made a module as you advised above and got it into SQL View:

SELECT GetTimeSlot([time]) AS [Time Periods], IIf([Till Number]="72" Or [Till Number]="73","Putney Library",IIf([Till Number]="77" Or [Till Number]="78","Tooting Library",IIf([Till Number]="74" Or [Till Number]="71","Battersea Library",IIf([Till Number]="75" Or [Till Number]="76","Balham Library",IIf([Till Number]="79" Or [Till Number]="86","Northcote Library",IIf([Till Number]="87" Or [Till number]="88","Earlsfield Library")))))) AS Library, Sum([Visitor Permits].[No Of Vouchers]) AS [SumOfNo Of Vouchers]
FROM ([Cash Trans Visitor Permits] INNER JOIN [Visitor Permits] ON ([Cash Trans Visitor Permits].[Transaction Date] = [Visitor Permits].[Issue Date]) AND ([Cash Trans Visitor Permits].[Account Number] = [Visitor Permits].[Account Number])) INNER JOIN [Visitor Audit] ON ([Cash Trans Visitor Permits].[Transaction Date] = [Visitor Audit].Date) AND ([Cash Trans Visitor Permits].[Account Number] = [Visitor Audit].[Account ID])
WHERE ((([Cash Trans Visitor Permits].[Till Number])="71" Or ([Cash Trans Visitor Permits].[Till Number])="72" Or ([Cash Trans Visitor Permits].[Till Number])="73" Or ([Cash Trans Visitor Permits].[Till Number])="74" Or ([Cash Trans Visitor Permits].[Till Number])="75" Or ([Cash Trans Visitor Permits].[Till Number])="76" Or ([Cash Trans Visitor Permits].[Till Number])="77" Or ([Cash Trans Visitor Permits].[Till Number])="78" Or ([Cash Trans Visitor Permits].[Till Number])="79" Or ([Cash Trans Visitor Permits].[Till Number])="86" Or ([Cash Trans Visitor Permits].[Till Number])="87" Or ([Cash Trans Visitor Permits].[Till Number])="88") AND (([Cash Trans Visitor Permits].[Transaction Date]) Between #9/1/2010# And #9/30/2010#))
GROUP BY GetTimeSlot([time]), IIf([Till Number]="72" Or [Till Number]="73","Putney Library",IIf([Till Number]="77" Or [Till Number]="78","Tooting Library",IIf([Till Number]="74" Or [Till Number]="71","Battersea Library",IIf([Till Number]="75" Or [Till Number]="76","Balham Library",IIf([Till Number]="79" Or [Till Number]="86","Northcote Library",IIf([Till Number]="87" Or [Till number]="88","Earlsfield Library")))))), GetTimeSlot([time])
ORDER BY GetTimeSlot([time]), Sum([Visitor Permits].[No Of Vouchers]) DESC;


I am trying to run the Query with the GetTimeSlot module and will let you know how it goes

Regards

 

[vbaInet]

You're using too many IIF()s in your query. Maybe it's time to think about normalizing your data. Consider using tables and lookup the value there.

 

[Pharaoh]

Hi,

That worked perfectly! Thank you so much for your help ^.^

 

[vbaInet]

You're welcome.

 

[gemma-the-husky]

I think your first time slot is set up incorrectly. You can never get the answer to be the second time slot.

 

[vbaInet]

Good spot Dave.

 

[Pharaoh]

Ah yes thanks for that, i will amend it.