Search results

I'm assuming, now, that there's some sort of error or corruption in the referenced table because, I tested each of the suggested expressions in a brand new database, with a table with just the required fields for the query and entered some test records and the query worked fine. I...

Rebuilding the tables is going to be a big task but, as you say, may be the only option for the long term. Not looking forward to this...lol.

Unfortunately I can't because of the data contained in it. However, I've had a look to see if there's a common denominator between the records but there isn't. Out of 3011 records that have a referral, only 132 show a result using this query. I thought it might only be picking up a value from...

Managed to figure how to implement the function, but I'm still getting the same result as all the other above suggestions.

I wasn't ignoring your suggestion, I was just confused as to how to implement it (where to input it).

I take on board what you say but how will the above allow me to report on the current location of the document? Should I have separate tables for each referral or separate fields on the Referrals table?

To give some context, the database tracks documents through their process within our company. So a document will be received and will be referred to a department (referral1). This department may need to then to refer it to another department, which would be referral2. There would be no more...

For some reason I can't multi quote (nothing happens after I select the multi quote icons). CJ_London and Marlan - I've tried both of your suggestions but both are returning exactly the same as my original expression. What's confusing me is this worked fine before, until I renamed some...

My goal is to return the value of the last of those fields that has a value, for each record. I don't get an error message. It simply just returns the entry in field Referral6, or a blank if the record doesn't have an entry in Referall6.

Can anyone please tell me if there is an issue with the following IIF statement that I have in a query? For some reason it's not working though it did before. It's only stopped working since I renamed a couple of the fields that are stated in the statement though I renamed these in the...

OK, I've actually figured out the issue, there were #NA errors in the table. But this brings me to another question...how can I get the above query expression to ignore records that have #NA in that field?

I have the following expression in a query field: When I add a text criteria to it and change back to datasheet view I get the Data Type mismatch error. Each of the fields referenced in the expression are text fields and the criteria I set is set in quotes so not sure why I'm getting this...

I've resolved it. Thanks for your help.

The field references you've quoted are different so I've had to rename some and add some in and my code is below but I'm till getting a syntax error

That's giving me an error - "Syntax error (missing operator) in expression "..."" Do I need to rename any other part of the expression except for the table name in the From statement?

How do I include it in my query?

I have the following IIF statement in an unbound textbox on a form but want show the result in a report for each relevant record but don't know how to do this or if it's possible. Can anyone please advise? =IIf(InStrRev([dept1] & ("/"+[dept2]) & ("/"+[dept3]) &...

It is. I've just been playing about and found how to do it within the query so sorted now. Thanks.

I'd like to know how to create a sort of "calculated" field in a report. I have my main table, where the report is generated from via a query. I have table 2 which lists staff in one field and their location in the second field, however, the location isn't stored in the main table. I want...

How can I use this IIf statement in a calculated table field? I've entered it, both with and without the first "=" but I get an error message ech time saying "cannot be used in a calculated field".