Access - SQL Syntax Error

[LiamBFC]

Right, I've got an Insert statement in VBA, however, I've got a syntax error that I can't spot.

Could do with a second pair of eyes.

The query is:

INSERT INTO ClientRequests ([REFERRAL NO], R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R14A, R14B, R14C, R14D, R19, R20, R15, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C14A, C14B, C14C, C14D, C19, C20, C15, W1, W2, W3, W4, W5, W6, W7, W8, W9, W10, W11, W12, W13, W14, W15, W16, W17, W18, W19, W20, WMisc" & _
", BedType, BedType2, BedType3, BedType4, Mattress, Mattress2, Mattress3, Mattress4, Base, Base2, Base3, Base4, DressingTable, DressingTable2, DressingTable3, ChestOfDrawers, ChestOfDrawers2, ChestOfDrawers3, Wardrobe, Wardrobe2, Wardrobe3, CabinetBed, CabinetBed2, CabinetBed3, CabinetBed4, CabinetTVHIFI, CabinetWallSide, SetteeBedSettee, EasyChair, EasyChair2" & _
", 3PCESuite, ChairsDiningStools, TablesDiningKitchenCoffee, TableDiningKitchenCoffee2, ElectricCooker, FridgeFreezer, WashingMachineDryer, Carpets, Carpets2, Carpets3, Misc) VALUES" & _
" (" & tRef & ", " & tR1 & ", " & tR2 & ", " & tR3 & ", " & tR4 & ", " & tR5 & ", " & tR6 & ", " & tR7 & ", " & tR8 & ", " & tR9 & ", " & tR10 & ", " & tR11 & ", " & tR12 & ", " & tR13 & ", " & tR14 & ", " & tR14A & ", " & tR14B & ", " & tR14C & ", " & tR14D & ", " & tR19 & ", " & tR20 & ", " & tR15 & "" & _
", " & tC1 & ", " & tC2 & ", " & tC3 & ", " & tC4 & ", " & tC5 & ", " & tC6 & ", " & tC7 & ", " & tC8 & ", " & tC9 & ", " & tC10 & ", " & tC11 & ", " & tC12 & ", " & tC13 & ", " & tC14 & ", " & tC14A & ", " & tC14B & ", " & tC14C & ", " & tC14D & ", " & tC19 & ", " & tC20 & ", " & tC15 & "" & _
", " & tW1 & ", " & tW2 & ", " & tW3 & ", " & tW4 & ", " & tW5 & ", " & tW6 & ", " & tW7 & ", " & tW8 & ", " & tW9 & ", " & tW10 & ", " & tW11 & ", " & tW12 & ", " & tW13 & ", " & tW14 & ", " & tW15 & ", " & tW16 & ", " & tW17 & ", " & tW18 & ", " & tW19 & ", " & tW20 & ", " & tWMisc & "" & _
", '" & tBed & "', '" & tBed2 & "', '" & tBed3 & "', '" & tBed4 & "', " & tMattress & "', '" & tMattress2 & "', '" & tMattress3 & "', '" & tMattress4 & "', '" & tBase & "', '" & tBase2 & "', '" & tBase3 & "', '" & tBase4 & "', '" & tDresser & "', '" & tDresser2 & "', '" & tDresser3 & "', '" & tDrawers & "', '" & tDrawers2 & "', '" & tDrawers3 & "'" & _
", '" & tWardrobe & "', '" & tWardrobe2 & "', '" & tWardrobe3 & "', '" & tBedside & "', '" & tBedside2 & "', '" & tBedside3 & "', '" & tBedside4 & "', '" & tCabTVHI & "', '" & tWallSide & "', '" & tSettee & "', '" & tEasyChair & "', '" & tEasyChair2 & "', '" & t3PCE & "', '" & tChairs & "', '" & tTables & "', '" & tTables2 & "', '" & tCooker & "'" & _
", '" & tFridge & "', '" & tWashing & "', '" & tHeater & "', '" & tHeater2 & "', '" & tCarpet & "', '" & tCarpet2 & "', '" & tCarpet3 & "', '" & tMisc & "')

[Static]

whats the error given?

(That is a HUGE SQL statement!)

[LiamBFC]

The error is:


Those values are meant to be empty at the moment.

[Static]

wait.. Found it:

, " & tMattress & "',

missing one little '

here it is fixed.

INSERT INTO ClientRequests ([REFERRAL NO], R1, R2, R3, R4, R5, R6, R7, R8, R9, R10, R11, R12, R13, R14, R14A, R14B, R14C, R14D, R19, R20, R15, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C14A, C14B, C14C, C14D, C19, C20, C15, W1, W2, W3, W4, W5, W6, W7, W8, W9, W10, W11, W12, W13, W14, W15, W16, W17, W18, W19, W20, WMisc" & _
", BedType, BedType2, BedType3, BedType4, Mattress, Mattress2, Mattress3, Mattress4, Base, Base2, Base3, Base4, DressingTable, DressingTable2, DressingTable3, ChestOfDrawers, ChestOfDrawers2, ChestOfDrawers3, Wardrobe, Wardrobe2, Wardrobe3, CabinetBed, CabinetBed2, CabinetBed3, CabinetBed4, CabinetTVHIFI, CabinetWallSide, SetteeBedSettee, EasyChair, EasyChair2" & _
", 3PCESuite, ChairsDiningStools, TablesDiningKitchenCoffee, TableDiningKitchenCoffee2, ElectricCooker, FridgeFreezer, WashingMachineDryer, Carpets, Carpets2, Carpets3, Misc) VALUES" & _
" (" & tRef & ", " & tR1 & ", " & tR2 & ", " & tR3 & ", " & tR4 & ", " & tR5 & ", " & tR6 & ", " & tR7 & ", " & tR8 & ", " & tR9 & ", " & tR10 & ", " & tR11 & ", " & tR12 & ", " & tR13 & ", " & tR14 & ", " & tR14A & ", " & tR14B & ", " & tR14C & ", " & tR14D & ", " & tR19 & ", " & tR20 & ", " & tR15 & "" & _
", " & tC1 & ", " & tC2 & ", " & tC3 & ", " & tC4 & ", " & tC5 & ", " & tC6 & ", " & tC7 & ", " & tC8 & ", " & tC9 & ", " & tC10 & ", " & tC11 & ", " & tC12 & ", " & tC13 & ", " & tC14 & ", " & tC14A & ", " & tC14B & ", " & tC14C & ", " & tC14D & ", " & tC19 & ", " & tC20 & ", " & tC15 & "" & _
", " & tW1 & ", " & tW2 & ", " & tW3 & ", " & tW4 & ", " & tW5 & ", " & tW6 & ", " & tW7 & ", " & tW8 & ", " & tW9 & ", " & tW10 & ", " & tW11 & ", " & tW12 & ", " & tW13 & ", " & tW14 & ", " & tW15 & ", " & tW16 & ", " & tW17 & ", " & tW18 & ", " & tW19 & ", " & tW20 & ", " & tWMisc & "" & _
", '" & tBed & "', '" & tBed2 & "', '" & tBed3 & "', '" & tBed4 & "', '" & tMattress & "', '" & tMattress2 & "', '" & tMattress3 & "', '" & tMattress4 & "', '" & tBase & "', '" & tBase2 & "', '" & tBase3 & "', '" & tBase4 & "', '" & tDresser & "', '" & tDresser2 & "', '" & tDresser3 & "', '" & tDrawers & "', '" & tDrawers2 & "', '" & tDrawers3 & "'" & _
", '" & tWardrobe & "', '" & tWardrobe2 & "', '" & tWardrobe3 & "', '" & tBedside & "', '" & tBedside2 & "', '" & tBedside3 & "', '" & tBedside4 & "', '" & tCabTVHI & "', '" & tWallSide & "', '" & tSettee & "', '" & tEasyChair & "', '" & tEasyChair2 & "', '" & t3PCE & "', '" & tChairs & "', '" & tTables & "', '" & tTables2 & "', '" & tCooker & "'" & _
", '" & tFridge & "', '" & tWashing & "', '" & tHeater & "', '" & tHeater2 & "', '" & tCarpet & "', '" & tCarpet2 & "', '" & tCarpet3 & "', '" & tMisc & "')

[LiamBFC]

Ah thanks.

Though somewhere I've still got a syntax error.
Can't spot it (sorry having a long day lol)

[Static]

still the same error?

[LiamBFC]

Not quite,

This time its:
Run-time error '3134'
Syntax error in INSERT INTO statement

[Static]

now the fun part:

1) make sure u have the same number of fields to values
2) make sure u arent trying to put a string into a number field and vice versa
3) create an "output" version of the SQL
meaning.. put that into code and Debug.Print it
Take that output and drop it into a query and try it...

[LiamBFC]

Sorted it.

Had 2 fields missing.

[Static]

cool thought that might be the case since u are dealing with so many fields

[LiamBFC]

113 :P, well now I've added in 3 more fields that have made it error >.<

I've added:

, Date, Month, Year

to the end of the fields and:

, '" & tDate & "', '" & tMonth & "', '" & tYear & "'

to the end of the values.

[Static]

, [Date], [Month], [Year]

those are reserved words.. u need the brackets (or change the names)
and if the field is a date time field then...

, #" & tDate & "#, '" & tMonth & "', '" & tYear & "'

[LiamBFC]

Ah, god I hate programming in Access.

If I had any other choice this would be a Web Based app ¬.¬