Inserting a string variable in SQL statement

[LOUISBUHAGIAR54]

Hi everyone,


I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.

I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.

The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctionation to be able to insert it into this statement.

StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))


Many thanks for any help.



Louis Buhagiar

 

[spikepl]

1. First, you need to make a working SQL statement that will run in the query designer. If you do that then you know what your VBA must produce. You current statement is not valid SQL. Build it in the query designer so you have a working statement.

2. proceed as described here: http://www.baldyweb.com/immediatewindow.htm

 

[LOUISBUHAGIAR54]

Thank you for your reply. The SQL statement which I have used to start with did work. This was as follows;

StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _
& " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _
& " WHERE (((WorkItems.IDcardNo)=[Forms]![FrmEmployees]![IDcardNo]) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null) AND (Not (WorkItems.Roster) Is Null));"

Now instead of the term [Forms]![FrmEmployees]![IDcardNo]) I would like to insert a text variable named numrukarta as by the time the above SQL is to be employed the form FrmEmployees will be closed.

I would like to have the exact punctuation to include around numrukarta within the SQL statement in vba. I have tried to find this everywhere but cannot find how this is done.

Many thanks for a specific reply.


Louis Buhagiar

 

[Minty]

Your SQL string is broken

& " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null)[COLOR="Red"];"[/COLOR] AND (Not (WorkItems.Roster) Is Null))

This cannot work the closing quote and semicolon should not be there.

Once you have moved those to the end of the line
You need to put your string in quotes;

 WHERE (((WorkItems.IDcardNo)= '" & numrukarta & "') AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null) AND (Not (WorkItems.Roster) Is Null)) ;"