Ask Your Question
0

access2base: Get value and use it in sql

asked 2016-02-16 12:02:47 +0200

cmpd gravatar image

Hello,

i am currently trying to write a macro with access2base that reads the current value of a textfield "IDTEST" and then executes an INSERT INTO SELECT STATEMENT using the current value of this field. I can querry the field by

Forms("Main").Controls("IDTEST").Value

but how can I use this value in an SQL statement?

Thanks in advance!

edit retag flag offensive close merge delete

4 Answers

Sort by » oldest newest most voted
0

answered 2016-02-25 00:40:58 +0200

jay Arr gravatar image

updated 2016-02-25 00:42:27 +0200

A word of caution: be very careful using string variables in the middle of an SQL statements.

You should ALWAYS test that the variable you are using does not contain either a single or double quote character. because if it does ... the SQL will not work!

You can use the runtime REPLACE function to replace the single or double quote character with TWO of the same and that way your SQL will work.

edit flag offensive delete link more
0

answered 2016-02-16 18:21:12 +0200

peterwt gravatar image

updated 2016-02-24 18:33:19 +0200

Assign the value of the textfield to a variable then use this variable in the SQL statement. I am assuming you are running the SQL in the macro.

EDITED 24/02/2016

I don't know if the syntax and punctuation is different in Access to Base but in standard Basic in Base the code snippet below adds a record to the table Table1 where the Name field value is in the variable strnew so it will contain New Name..

strnew="New Name"
strSQL="INSERT INTO ""Table1"" (""Name"") VALUES ('" + strnew + "')"

If the value was ('strnew') then the Name field would contain strnew, the name of the variable. To get the contents of the variable requires the value to be ("' + strnew + '").

Looking at the info at Use Variables in SQL. this might work -

DoCmd.RunSQL("Insert Into tantworten(IDGRUPPE,IDFRAGE,IDTESTUNG) Select " & Wert & " as IDTESTUNG, tfragen.IDGRUPPE, tfragen.IDFRAGE from tfragen")

edit flag offensive delete link more

Comments

How? The basic variable is not regonized as correct syntax

Sub Value Wert = Forms("Festungen").Controls("IDTESTUNGEN").Value DoCmd.RunSQL("Insert Into tantworten(IDGRUPPE,IDFRAGE,IDTESTUNG) Select Wert as IDTESTUNG, tfragen.IDGRUPPE, tfragen.IDFRAGE from tfragen") END Sub

cmpd gravatar imagecmpd ( 2016-02-16 18:26:54 +0200 )edit

@cmpd I have added to my answer.

peterwt gravatar imagepeterwt ( 2016-02-24 18:34:35 +0200 )edit

Thanks to both of you. It worked like a charm!

cmpd gravatar imagecmpd ( 2016-02-26 11:15:17 +0200 )edit
0

answered 2016-02-17 13:13:44 +0200

JPL gravatar image

Look at Use Variables in SQL.

JPL

edit flag offensive delete link more

Comments

Thank you very much!

cmpd gravatar imagecmpd ( 2016-02-24 11:46:09 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-02-16 12:02:47 +0200

Seen: 274 times

Last updated: Feb 25 '16