Query criteria help/ Whats wrong with my expression?

[bo8482]

Hi

I'm relatively new to Access and have been trying to create what I thought would be an easy expression.

My main table is (client field = primary key and in relationship with table below):
Client | Client name |CHAPS Charge
123 |ABC | £20
999 | XYZ | £20

I have another table with:
Client | Payment Method
123 | CHAPS
999 | BACS

I now want to run a query with these headings that returns the following results:
Client | Client Name | Payment Method |Chaps Charge
123 | ABC | CHAPS | £20
999 | XYZ | BACS | £0

I.e. where Payment method = Chaps, return the value within CHAPS. However at the moment my table is returning £20 for client 999.

I have run the following expression in criteria in my query but I keep getting data type criteria mismatch error messsages:

IIf([Payment method]="BACS","",[CHAPS Charge])


Can anyone tell me what I'm doing wrong? I know this should be relatively straightforward but as I say, I'm new to all this!!

Many thanks for any help you can offer me

Brian

 

[pbaldy]

Welcome to the site. That looks like it should work. Are you sure of the contents field are exactly "BACS", and there are no trailing spaces or anything?

 

[bo8482]

Thanks for the quick reply!

I have copied and pasted it straight from Access so what you see there is what I have. I was a bit concerned about the spaces etc, and I note that if I remove the spaces from 'payment method' and 'CHAPS Charge' I don't get an error message but I do get a dialog box saying "Enter query parameter for paymentmethod" (/chapscharge). Does that help at all?

 

[pbaldy]

No, I meant spaces in the data, like "BACS ". The spaces in the field names aren't recommended, but bracketing them as you have should allow them to work.

Another thought is whether the field displaying BACS is actually a lookup field, where some sort of ID is actually being stored.

 

[JANR]

IIf([Payment method]="BACS","",[CHAPS Charge])

If you are writing this expression in querygrid depending on your regionalsetting changing the commas to semicolon could help.

IIf([Payment method]="BACS[COLOR=red][COLOR=black]"[/COLOR][COLOR=red];[/COLOR][COLOR=black]""[/COLOR];[/COLOR][COLOR=black][[/COLOR]CHAPS Charge])

On my system that's what I have to do if I write expressions in query design mode or controlSource for formcontrol's

JR

 

[bo8482]

OK first off, the semi-colons didn't work, it gave me an invalid syntax error (my computer is setup for UK settings for what its worth). But thanks for the tip...

Secondly, there is no space after the BACS. In the table design I used "Lookup Wizard", then "enter text you want", then used "BACS" and just below it "CHAPS".

Does that shed any light? Thanks for your help!

 

[bo8482]

I should probably add I'm adding this expression in the "criteria" row in the design view of a query....I presume thats the right place...

 

[pbaldy]

Table level lookups are not recommended:

http://www.mvps.org/access/lookupfields.htm

because they confuse what's actually being stored. I suspect you have an ID or something. Can you post the db?

 

[bo8482]

Hi
Unfortunately I can't post the DB as it has telephone numbers/names etc in it...unless there's a way of excluding the data?

Essentially I have a data entry form and I want users to select either CHAPS or BACS to populate the table - I figured a table lookup was the way to go about it to prevent typos. What other way can you do it? I'm worried if they type the text manually and mistype it (such as BASC) then that won't show up on a report and someone won't get paid....any ideas?

 

[pbaldy]

Can you post a screen shot of that table in design view, with focus on that field and the lookup tab shown? I never use the things, so I'm having trouble visualizing what you've got.

The alternative is a related table. In your forms, you use a combo box that gets its selections from the related table, and the user is limited to those selections.

 

[bo8482]

OK please see attached screenshot...

If I can get it working this way that would be great as I had a nightmare trying to get table relationships working etc and would rather go down that route as last resort!

Thanks for your help

 

[JANR]

I should probably add I'm adding this expression in the "criteria" row in the design view of a query....I presume thats the right place...

No.. I think you have to put it in the fieldRow and make a calculated field.

CHAPS CHARGE: IIf([Payment method]="BACS","£ 0","£ " & [CHAPS Charge])

This makes it a calculated field in your query. I'v added the currency type in the expression.

JR

 

[bo8482]

JNAR - you are a legend! It works! Thanks very much, I knew I was doing something stupid.

Thanks pbaldy for the tips too...cheers!

 

[JANR]

Glad to help.

JR

 

[pbaldy]

I totally missed that post. Good catch JR.