Query to return 0 is no record is found (1 Viewer)

[tmyers]

I am trying to make a query that returns 0 if no record is found which would mean all conditions are met and the base query would not return anything but I am getting a syntax error.

SELECT ISNULL(
SELECT tblMultiConductorCut.TicketID, tblMultiConductorCut.MultiComplete
FROM tblMultiConductorCut
WHERE (((tblMultiConductorCut.TicketID)=[Forms]![frmWireRoom]![TicketID]) AND ((tblMultiConductorCut.MultiComplete)=False)),0);

 

[Gasman]

Use DCount() ?

Are you confusing IsNull with NZ() ?

 

[tmyers]

I thought about DCount but after reading various post from Stack Overflow and such, most people seem to suggest ISNULL over DCount for some reason. Ill give DCount a try.

 

[Gasman]

Care to post the links?

 

[GPGeorge]

I thought about DCount but after reading various post from Stack Overflow and such, most people seem to suggest ISNULL over DCount for some reason. Ill give DCount a try.

IsNull is the TSQL parallel to Access' Nz(). Are those posts you referred to about SQL Server, or about Access?

 

[tmyers]

Return a value if no rows are found in Microsoft tSQL

Using a Microsoft version of SQL, here's my simple query. If I query a record that doesn't exist then I will get nothing returned. I'd prefer that false (0) is returned in that scenario. Looking fo...

stackoverflow.com

Completely missed that is was TSQL. I have also been working in SQL Server so completely missed the fact that it was not a function in Access.

 

[plog]

Every SELECT needs a FROM. I count 2 SELECTS, 1 FROM.

I am trying to make a query that returns 0 if no record is found

That's one condition, now what is suppose to happen when not 0 records ae found? What do you expect the query to return when there are results?

My gut is telling me that you've gone down a bad path for your ultimate goal and seeking help for that bad path. You are asking for help with an issue that isn't part of the best solution for the big picture. So, what is the big picture? What are you ultimately trying to do? This is an intermediate step--what do you plan on doing with that 0?

 

[tmyers]

The ultimate plan is to use this step to verify all records are flagged as complete, which if they are, the query would not return anything since everything WOULD be complete. I would then allow a process to finish and close out an order.

 

[plog]

That made very little sense. How's this---you build this query that returns 0 records. What would the next step be?

 

[Gasman]

The ultimate plan is to use this step to verify all records are flagged as complete, which if they are, the query would not return anything since everything WOULD be complete. I would then allow a process to finish and close out an order.

Sounds ideal for a DCount ?

 

[theDBguy]

I have also been working in SQL Server so completely missed the fact that it was not a function in Access.

Just FYI, IsNull() is definitely a function in Access. It just does a different job than the one in T-SQL.