concanating SQL statements

[PhilipEwen]

Hi,
I am running an SQL statement in VBA so that when a button is pressed, it takes the values from 2 text fields and puts them in a table in the correct fields.

I can do this fine with 1 statement
ss = "INSERT INTO [resorts](resortname) VALUES (txt_resort_name)"

but this only inserts into the 1 field (resortname)
I am trying to add data to 2 fields at the same time, so how do i join the SQL statements together?

ss = "INSERT INTO [resorts](resortname) VALUES (txt_resort_name);"
ss = ss & "INSERT INTO [resorts](transfercost) VALUES (txt_trans_cost)"
Doesn't work - how do i do it ??

Thanks in advance for any help

Phil.

 

[SteveA]

The SQL statement has two arrays. The first contains all the fields to be updated, and the second holds all the new values for each field.

Try the following. Hopefully it will work:

ss = "INSERT INTO [resorts](resortname, transfercost) VALUES (txt_resort_name, txt_trans_cost);"

Cheers,
SteveA

 

[PhilipEwen]

Thanks very much for your help on this....it worked.
I am trying to do the same for a hotels section and have the following
ss = "INSERT INTO [hotels] (hotelid,hotelname,hotelsresortid,date,hb,al,sc,bb) VALUES (hotelid,update_hotelname,update_hotelsresortid,txt_from,txt_hb,txt_al,txt_sc,txt_bb);"

Why do i keep getting a 'runtime error 3134 - INSERT INTO syntax error' ??

any ideas

thanks

Phil.

 

[SteveA]

I think the problem is that the field 'date' is a reserved word in SQL and cannot be used as a field name.

Cheers,
SteveA

 

[PhilipEwen]

thanks for that. This 'date' is actually referencing the 'date' field in my table - should i therefore rename that field do you think ?

Thanks for your help.

 

[PhilipEwen]

I have changed the date anyway - just incase.

i have a snippet of the code below. as you will see i am trying to use the variable 'entrydate' as the data to enter into the table ( i would then add 1 to the entrydate each time to make the entries for all those dates )
I keep getting 'Enter Parameter entrydate' coming up when i run this, which means it doesn't know what entry date is. When i watch that variable, it does realise the date and is correct - so why can't it put it in the table ?? am i defining it incorrectyl or something ??

Dim entrydate As Date

'set up variables
entrydate = Forms!hoteldetails!txt_from 'set the entry date to enter the correct data to the From field
counter = 1 ' counts the loops


firstdate = Forms!hoteldetails!txt_from 'firstdate for calculation
lastdate = Forms!hoteldetails!txt_to ' last date for calculation

dateinterval = DateDiff("d", [firstdate], [lastdate]) 'sets the number of days between the dates
dateinterval = dateinterval + 1 ' add 1 to include last day

'start loop
For counter = 1 To dateinterval
ss = "" 'clears any old data
ss = "INSERT INTO [hotels] ( hotelid,hotelname,hotelsresortid,[datefield],hb,al,sc,bb ) VALUES (Forms!hoteldetails!hotelid,Forms!hoteldetails!hotelname,Forms!hoteldetails!hotelsresortid,entrydate,Forms!hoteldetails!txt_hb,Forms!hoteldetails!txt_al,Forms!hoteldetails!txt_sc,F orms!hoteldetails!txt_bb);"

'DoCmd.SetWarnings (False)
DoCmd.RunSQL ss


Cheers

Phil.

 

[SteveA]

Given that you are not actually changing or manipulating the field txt_from, why not try using that field in the INSERT statement. I haven't tried this. Only guessing

SteveA

 

[PhilipEwen]

Hi steve, problem solved from another Steve
Thanks for you input though