asked on

sql cant find input table or query error

sql cant find input table or query error:

Using the sql below: I keep getting a error message on the syntax ?

strsql_sql = "INSERT INTO [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily]([NSC_ID_Racfid], [Admin_Logged_Out])" & vbCrLf
strsql_sql = strsql_sql & " VALUES('" & racfid & "', #" & dCurr & "#) where (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "');"

Thanks
fordraiders

slightwv (䄆 Netminder)

When using VALUES, there cannot be a WHERE clause.

A WHERE clause somes into play when you "insert into select".

Fordraiders

ASKER

net, ok confused..isnt this a INSERT INTO SELECT statement ?

slightwv (䄆 Netminder)

I don't see the word "select".

Inserts have two forms:

insert into some_table values('val1','val1');

and

insert into some_table select some_col1, some_col2 from some_other_table;

You cannot mix and match.

John Tsioumpris

Probably you want

INSERT INTO  [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily]([NSC_ID_Racfid], [Admin_Logged_Out])
SELECT [NSC_ID_Racfid], [Admin_Logged_Out]
FROM YOURTABLE
WHERE (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "')

Open in new window

aikimark

You are missing a concatenation operator here

vbCrLf
strsql_sql

Your field names are not in the correct format.

Fordraiders

ASKER

john, net

OK still confused on inserting a parameter value ? or variables ?

VALUES('" & racfid & "', #" & dCurr & "#)

so this cant be done ?

fordraiders

slightwv (䄆 Netminder)

You can insert hard-coded values.

In the original SQL you posted, you have "VALUES" and "WHERE" in the string. I'm saying you cannot have "VALUES" and "WHERE" in the same insert statement.

John Tsioumpris

VALUES is only needed when you already have them as statically set...if you want a dynamic insert then you don't use VALUES as the Select will feed the insert query.

Fordraiders

ASKER

hmmm, I guess and Update statement would be more approriate ?

strsql_sql = "UPDATE [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily] SET [Admin_Logged_Out] = #" & dCurr & "# WHERE (DateValue([Admin_Logged_In]) = Date and [NSC_ID_Racfid] = '" & racfid & "');"

fordraiders

aikimark

that looks like a correctly formatted Update statement string.

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

getting "too few parameters expected one" ?

table name is correct
field names are correct

strsql_sql = "UPDATE [dbo_t_nsc_trackcode_AdminLogIn_and_AdminLogOut_Daily] SET [Admin_Logged_Out] = #" & dCurr & "# WHERE (DateValue([Admin_Logged_In]) = Date) and [NSC_ID_Racfid] = '" & racfid & "'"

aikimark

please post the contents of the strsql_sql variable.

Fordraiders

ASKER

thanks all...used the update statement.