SQL "INSERT INTO... VALUES (yes/no)" statement help

[Futures_Bright]

Hi all,

I'm now trying to speed up data entry within my database and have hit a brick wall with one part.

Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).

The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform.

When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).

I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:

DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"

Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:

Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...

.

Hopefully that's all the necessary information, can anyone help me sort this please? Let me know if there's anything else you need to know or if any of the above is unclear.

 

[pbaldy]

The sets of double quotes inside the string are probably unnecessary, but need to be single quotes because the first one ends the string begun after RunSQL. This may also prove useful:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Futures_Bright]

Thanks pbaldy, I did think there were too many quotation marks in there but when I took any out I got 'Syntax Error'.

I've just taken your advice and put the string in a variable and it seems to think the end of the string is somewhere in the middle so I presume that is the problem! I'll run through my statement again when it isn't a sunny Friday afternoon (we don't get many in the UK!) and let you know how I get on.

 

[pbaldy]

My daughter just moved back to the US after 3 years in England. She would agree with you about the sunny days.

 

[DavidAtWork]

I've always found this to work:
DoCmd.RunSQL("INSERT INTO Applicability ( AppRelClause, """ & strAppFieldList & """) VALUES (' " + Str(Me.Clause_ID) + " ', """ & strSubAppFieldList & """)")

where Me.Clause_ID is a number and the other variables are strings.
If this fails, you might want to step through this and check the values of strAppFieldList and strSubAppFieldList, I've never tried this string variable approach to listing field names and field values. I would start with just the first 2 fields: AppRelClause and first field from strAppFieldList and build it up field by field until it fails

David

 

[gemma-the-husky]

i NEVER use double quotes. I also use chr(34) instead.

so:

strg = "INSERT INTO Applicability ( AppRelClause, " & chr(34) & strAppFieldList & chr(34) & ") values "

now the next problem is that each field being added needs wrapping correctly
so you need to be able to format STRINGS that look like this

"numberfield = 4"
"textfield = "sometext" " (chr(34) avoids the dbl quote problems)
"datefield = #somedate#"
for boolean fields I prefer
"booleanfield = " & true (or false, or boolean variable)

then concatenate them altogether as appropriate.

after all this, you should be able to

domcmd.runsql strg (the full concatenated string)

 

[Futures_Bright]

Thank you for your advice Gemma & David, unfortunately my time to test this has been limited by other projects (hopefully should try before the weekend).

Regarding the correct format, other than AppRelClause the remaining fields within the two strings are all boolean yes/no - so I guess as part of the generation for the variable I want each item to be something like:

 VALUES (... , 'Booleanfield = " & Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![[COLOR="Red"]strFieldName[/COLOR]]"', ...

Where strFieldName is the name of a field. Does this look correct?