Run-time error 3075 (syntax error (comma) in query expression...)

[ColinH]

I have a make-table query based on several other tables together with user input, and it works well. However, I don't think I can't use the query as it is. I think I need to create the same SQL text in VBA and add various WHERE values which the user creates by selecting values on a form.
The query includes an IIF clause to format two fields, but the SQL text from the query always fails at the VBA command "DoCmd.RunSQL (strSQL) with the run-time error above. I realise this is an Access SQL error not VBA.

The two fields are parts of a UK map reference for biological wildlife sites and consist of two letters and between two and ten numbers. Sometimes the site has been entered with no map reference and I use the IIF function to detect this. Here is the code from the query:

IIf(IsNull([Site].[Map_ref_letters])," ",[Site].[Map_ref_letters] & " " & Mid$([Site].[map_ref_numbers],1,5) & "/" & Mid$([Site].[map_ref_numbers],6,5)) AS [Map ref],

My VBA code is the same except that the spaces and forward slash inserted into the Map ref field have doubled quotes, e.g. ""/""

 

[Minty]

You would need to use '/' in the middle of your concatenation.

If you Debug.Print strSQL it shouldn't have any double quotes " in it unless you triple escaped them

""""

Which gets very messy

 

[ColinH]

Thanks for your reply Minty. I think the double quotes are resolving correctly as you can see from the error report above, the problem might be an extra comma but I can't see where. They also look correct in the Immediate window:

IIf(IsNull([Site].[Map_ref_letters])," ",[Site].[Map_ref_letters] & " " & mid$([Site].[map_ref_numbers],1,5) & "/" & mid&([Site].[map_ref_numbers],6,5)) AS [Map ref],

 

[namliam]

why create a table in the first place, you seem to be able to get the data in a query duplicating the data by way of a create table query is a seriously bad idea...

 

[namliam]

mid&([Site].[map_ref_numbers],6,5))

Shouldnt that be
mid$([Site].[map_ref_numbers],6,5))

or even
mid([Site].[map_ref_numbers],6,5))

 

[ColinH]

That's exactly right, mailman that character should be $ not &. The VBA works correctly now, thanks for your help.

..and a merry Christmas to you!

 

[plog]

First, why do you need to make a table?

MAKE TABLES are huge indicators of a poor design or understanding on the developers part of how databases are to work. Data shouldn't be moved hither and yon and manipulated and stored. All of that should be one in a query and that query should be referenced when that manipulated/moved data is needed.

Second and reinforcing my initial diagnosis:

IIf(IsNull([Site].[Map_ref_letters])," ",[Site].[Map_ref_letters] & " " & mid$([Site].[map_ref_numbers],1,5) & "/" & mid&([Site].[map_ref_numbers],6,5)) AS [Map ref],

Discrete pieces of data need to be stored discretely. In simpler terms, every piece of data that means something on its own goes into its own field. You are both extracting poorly stored data and inserting poorly stored data with that line of code.

If the first 5 characters of [Site].map_ref_numbers] means something on it own(and it does because you are extracting it) then it should be in its own field, then the last 5 characters should go in their own field. Same with the receiving table, [Map ref] should be 3 fields--one to hold the data from [Map_ref_letters] and 2 for [map_ref_numbers], not just 1 field where you encode and jam everything in.

I think this whole thread is about an issue that shouldn't even exist.

 

[ColinH]

Plog, the short answer to your comment is that I have a large database of biological records (>50000) which I need to sort by different fields and combinations of fields to produce reports, and I couldn't think of another way to achieve this.
A longer answer is that the UK mapping system is a bit odd, with its 2 letters and up to 10 digits (e.g. AB1234567890). The two letters represent one of a series of 100 km squares of the UK. The first five digits represent 'eastings', the distance east from the bottom left hand corner of a square, then the last five the distance north ('northings'). I store the letters in one field and the numbers in another. I receive records from volunteers at various levels of accuracy from "10 km square" which have the form AB16 to "100 m square" as AB123678. When these are entered into a 'site' form they are all padded to 10 digits for accuracy using dashes. So AB16 becomes AB1----6---- and AB123678 becomes AB123--678--. To make this more readable in reports I insert a space after the letters and a "/" between the eastings and northings.

Although map references in the UK are conventionally written as a single 'string' of text, I take your point that there are three discrete items of data here. If I was designing the database again I would probably use three fields. Also, if you can suggest a better way of interrogating the data I'd be glad to hear it.

 

[plog]

I need to sort by different fields and combinations of fields to produce reports, and I couldn't think of another way to achieve this.

I don't understand how a new table facilitates that. Tables by their very nature have no order. Queries have ORDER BY clauses and MS Access Reports allow you to sort as well and can be based on queries. So the MAKE TABLE makes no sense given that reason.

To make this more readable in reports I insert a space after the letters and a "/" between the eastings and northings.

Data presentation doesn't dictate storage. In the query you posted above you demonstrated that a query can produce the formatted data you wanted--the code I quoted initially does that. You can use that code in a SELECT query based on a properly structured table and get that result.

I think done properly you could reduce this whole process to a button click. Open your database, it auto loads a form on which is a button which when clicked opens a series of reports and gives you everything you need without you having to do anything other than pressing that button.