I've asked about this before, but there's a bug I vcan't fix...

[Nightowl4933]

Plog very kindly provided a solution to a problem I had with multiple search criteria, but one of the options is causing me a bit of a headache as it doesn't work - and i've only just noticed it!

Basically, I'm using a series of If... Then... to concatentate a search string in to a workable SQL query, but when Ithis is based on field in a table which is a lookup field, I get prompted to 'Enter Parameter Value' and I'm running around in circles trying to fix it!

I'll try to explain:

tblAllApplications has a lookup field 'PlanningAuthority' from tblPlanningAuthority. The SQL code for this lookup is:

SELECT tblPlanningAuthority.ID, tblPlanningAuthority.Authority
FROM tblPlanningAuthority
ORDER BY tblPlanningAuthority.[Authority];

In tblPlanningAuthority the datatype for 'Authority' is Text.
In tblAllApplications the datatype for 'PlanningAuthority' is Long Integer - but I don't know (or remember) why I did this!

I get prompted to 'Enter Parameter Value' when the On_Click Event Procedure includes the 'PlanningAuthority' field.

I added another Command Button to the form and used a cut-down version of the Event Procedure, but the result was the same. :banghead:

I also used a simple query based on tblAllApplications with the same criteria results the Event Procedure would return:

SELECT tblAllApplications.PlanningAuthority
FROM tblAllApplications
WHERE (((tblAllApplications.PlanningAuthority)=11));

...and I get the correct results.

Would someone be kind enough to give me a steer, please?

Edit: Sorry, I forgot to add this all worked perfectly before the database was split.

 

[pbaldy]

Using a lookup field was your first mistake.

The problem is you see the text in the table, but the long integer is what is actually stored. In your code, you need to provide the numeric value, not the text value.

 

[MarkK]

...but one of the options is causing me a bit of a headache as it doesn't work...
...
I get prompted to 'Enter Parameter Value' when the On_Click Event Procedure includes the 'PlanningAuthority' field.

If a SQL statement contains an identifier that the SQL parser can't find in any of the referenced tables, it assumes it is a parameter, and prompts you, so if this prompt comes up unexpectedly, look for a misspelled fieldname, or a fieldname from a table that isn't present in the FROM clause.

 

[Nightowl4933]

Using a lookup field was your first mistake.

The problem is you see the text in the table, but the long integer is what is actually stored. In your code, you need to provide the numeric value, not the text value.

I tried to use both options, because I get the problem with using a Lookup. The trouble is, whichever option I decide on, there's always an error!

When I use the numeric value, I get this error. When I use the text value, and include the resulting string in quotes, I get a different error!

 

[pbaldy]

Hard to fix without seeing the code, or playing with the db.

 

[Nightowl4933]

Hi guys. Sorry for not responding sooner.

OK, I can upload the front end of the database, but obviously you won't be able to see the data. Would this be enough to get an idea of where I need to go, please? If not, I could upload the pre-split db, but would request discretion

To be more specific about the 'other error', though:
I tried to fix this by changing the value of strAuthority from

strAuthority = comAuthority.Column (1)'
...to...
strAuthority = comAuthority.Value (of the field on the form)

...and then changed the If... Then... string:

If strAuthority <> "" Then strFilter = strFilter & " AND [PlanningAuthority] = " & strAuthority
...to...
If strAuthority <> "" Then strFilter = strFilter & " AND [PlanningAuthority] = '" & strAuthority &"'"

...but I get a Run-time error '3464': Data type mismatch in criteria expression. error, instead.

Would a suitable work-around (now I've earnt my lesson with Lookups!) be to set the variable (strAuthority) within the VBA code? There are only 13 of them, so it shouldn't be too difficult?

Thanks

 

[Nightowl4933]

I've changed the search criteria field from a Lookup to a ComboBox where I've added the values, but the problem persists. I get either a Run-time error '3464' data mismatch error if I use the text value of the field, or a 'Enter Parameter Value' if I use = comAuthority.Column(1) as the value.

Again, it works on the combined database, but when I make the same changes to the front end database, it doesn't.

Argghh!

 

[Minty]

Do you have a separate table for the Authority - if not make one and use that as the row source for the combo box.
Your problem is not that the criteria field is a lookup but that the source is. You need to remove the all lookup's from the underlying tables.

 

[Nightowl4933]

Hi Minty,

I do have a separate table for the Authority - and the Authority field in the Applications table is a lookup to it.

I've just (literally) sorted that bit, and I'm with you on changing the lookup field in the applications table.

Could I do this with a simple update query for that field, basically changing the ID to text value?

Thanks,

Pete

 

[jdraw]

In your separate Authority table, what are the fields and data types.
If AuthorityID in Authority table is an autonumber PK, then AuthorityID in your Applications table should be a Long Integer and act as FK to the Authority table.

 

[Minty]

Well - you need to if you have existing data.
I'm all for storing an ID personally as it's just much easier to have a numeric PK field for the linked item. If someone makes a typo in the field text and it gets stored in your data, then corrected in the lookup table, you end up with two distinct values that should be the same, whereas in a table with a ID it would simply pull in the corrected value.

There are some on here who disagree with this for single field lookup tables, but I find it simpler to work with particularly with SQL Server linked tables.