how to count occurance of field

In access I have a table and within a QUery I want to check to see if a field is not Null and if so multiple 2 x 25).

So if [LarRecvDate] is not null then write 2 *25.

What would the syntax in the query be?
seamus9909Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
oops, sorry, copied the wrong one


SELECT tblClaim1.ClaimID, tblClaim1.ClaimNo, tblClaim1.Claimant, tblClaim1.LARRECVDATE, tblEorRecvd.EORRecDate, IIF(tblClaim1.[LARRECVDATE] & ""<>"",1*25,Null) as Owed
FROM tblClaim1 INNER JOIN tblEorRecvd ON tblClaim1.ClaimID = tblEorRecvd.ClaimID
0
 
worthyking1Commented:
You're going to need to be a bit more specific on exactly what you're trying to do in order for us to help you.
0
 
seamus9909Author Commented:
ok in the row from the table I want to evaluate.

A field called  "LarRecved" can be null or not null.
if the field is not null I want to calculate using an expression in the query.

So if LarRecved is not null multiply 2 *25.

Does this help?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
select [LarRecved], IIF([LarRecved] & ""<>"",2*25,Null)
from tableX


why not just use 50 instead of 2 * 25


post sample values from field LarRecved  and the result you expect to get when LarRecved  is NOT null and when LarRecved is NULL
0
 
seamus9909Author Commented:
So either there is a date in LARRECVDATE or its empty


select [LARRECVDATE], IIF([LARRECVDATE] & ""<>"",2*25,Null)


I could just enter 50 that is true.  When I entered the above syntax it is giving me an error .

I am missing something?
0
 
Rey Obrero (Capricorn1)Commented:
you have to include the "From TableName"


select [LARRECVDATE], IIF([LARRECVDATE] & ""<>"",2*25,Null)
From TableName
0
 
seamus9909Author Commented:
Owed: select[LARRECVDATE], IIF([LARRECVDATE] & ""<>"",1*25,Null) from tblclaim1

still giving me a syntax error.
0
 
Rey Obrero (Capricorn1)Commented:
copy this as the one COMPLETE query

select [LARRECVDATE], IIF([LARRECVDATE] & ""<>"",1*25,Null) as Owed
from tblclaim1

OR post the whole query you are using
0
 
seamus9909Author Commented:
Here you go


SELECT tblClaim1.ClaimID, tblClaim1.ClaimNo, tblClaim1.Claimant, tblClaim1.LARRECVDATE, tblEorRecvd.EORRecDate
FROM tblClaim1 INNER JOIN tblEorRecvd ON tblClaim1.ClaimID = tblEorRecvd.ClaimID
select [LARRECVDATE], IIF([LARRECVDATE] & ""<>"",1*25,Null) as Owed
from tblclaim1;
0
 
Rey Obrero (Capricorn1)Commented:
here is the query


SELECT tblClaim1.ClaimID, tblClaim1.ClaimNo, tblClaim1.Claimant, tblClaim1.LARRECVDATE, tblEorRecvd.EORRecDate, IIF(tblClaim1.[LARRECVDATE] & ""<>"",1*25,Null) as Owed
FROM tblClaim1 INNER JOIN tblEorRecvd ON tblClaim1.ClaimID = tblEorRecvd.ClaimID
from tblclaim1;
0
 
seamus9909Author Commented:
It says syntax error missing operator when I try and run it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.