Query 3

[vincentharris]

I have a query that I created. I took two tables: Table 8AM and Table 10PM. I do a comparison to see how many new tickets were created between 8AM and 10 AM. In this case it was twelve. I got 12 by scrolling down in the query and where I see a blank in under the 8AM column that is counted as a new ticket. I would like to know if I use Is Null under the field 8AM and it only pull those 12 tickets, which I need, how would I create a formula in Access that would let me see the word NEW TICKET in the query?

 

[PHV]

What is your actual SQL code ?

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

 

[jrbarnett]

Look at the Nz function to substitute something with your own text.

John

 

[randy700]

Something like...

SELECT FirstField, SecondField, iif([8AM] = "", "NEW TICKET", [8AM]), As NewTicket FROM...

...not tested...

Randy

 

[vincentharris]

Thanks!!! I will test it out. Do you know by chance how I would get the previous date in a report in Access. I know if I use =Now()it will give me the current date but how to get the previous date.

 

[PHV]

Date()-1

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

 

[vincentharris]

This is what I have so far

Incident:IIF([Maximo Data 10AM 07102009] ="Is Null", "New Ticket")]

I am comapring data from Maximo Data 10AM 07102009 to Maximo Data 12PM 07102009

I have 25 blanks meaning they are new tickets. I want it to show "New Ticket" where there is a blank space.

 

[PHV]

Incident:IIF([Maximo Data 10AM 07102009] Is Null, "New Ticket", "Not Null !")


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

 

[vincentharris]

I tried that formula and a pop up box comes up and it says Enter Paramter Value....I just want the blanks to say New Ticket. Also how can I get SnapShot Viewer in Access?

 

[PHV]

Enter Paramter Value...
value for what ?

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

 

[vincentharris]

The box says Enter Parameter value below that it says Maximo Data 10AM 07102009. The below it prompts for a value to be entered.


Also how do I attached SnapViewer to Access?

 

[PHV]

So, in your query you have no field named "Maximo Data 10AM 07102009" !

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

 

[SkipVought]

I am comapring data from Maximo Data 10AM 07102009 to Maximo Data 12PM 07102009

In What Field is this data in?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vincentharris]

The two tables I am using are called Maximo Data 10AM 07102009 and Maximo Data 12PM 07102009. The field name is

Incident which is in both tables. In both tables the field names are the same. I am just comparing the two tables to see what new tickets that appear from 10am to 12 pm. In this case there were 25 new tickets. I want to be able to have a formula in the query that will say New Ticket in those 25 blanks.

 

[SkipVought]

The field name is

Incident which is in both tables

Incident:IIF([Maximo Data 10AM 07102009].Incident ="Is Null", "New Ticket")]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vincentharris]

Now it says that the expression entered contains invalid syntax.

 

[SkipVought]

I think you may want...

NewIncident: IIF([Maximo Data 10AM 07102009].Incident Is Null, "New Ticket","Not Null")]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vincentharris]

Skip:

I used this formula - NewIncident: IIf([Maximo Data 10AM 07102009].[Incident] Is Null,"New Ticket") and it seem to work fine to where it highlighted the blanks as a new ticket.

When I tried to compare Maximo Data 12PM to Maximo 3PM with this formula NewIncident: IIf([Maximo Data 12PM 07102009].[Incident] Is Null,"New Ticket") the same message as before

"The expression entered contains invalid syntax."

What am I doing wrong??

 

[r937]

IIf requires three parameters -- you prolly want Nz

change this --

NewIncident: [red]IIf[/red]([Maximo Data 10AM 07102009].[Incident] Is Null,"New Ticket")

to this --

NewIncident: [blue]Nz[/blue]([Maximo Data 10AM 07102009].[Incident], "New Ticket")

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vincentharris]

Skip:

I was able to compare 8AM to 10AM and 10AM to 12PM and 3PM to 5PM. When I try to comapre 12PM to 3PM it gives me the pop up box Enter Paramter Value. I use the formula NewIncident: IIf([Maximo Data 12PM 07102009].[Incident] Is Null,"New Ticket") and it gives me the pop up box. What am I doing wrong.

Also how to get Snapshot viewer into Access?