Building SQL Statement

[Andrea]

I'm currently creating an Access 2000 database to keep track of
employee vacation hours. I have a form in which the number of hours
requested off by an employee is entered into a text box. Then the
employee's remaining vacation hours are updated through a SQL update
statement. I build the SQL statement through VBA and then execute it.

Here is what my SQL looks like:
strSQL = "UPDATE Employee SET VacationTime = "
strSQL = strSQL & intNewVacation & ", "
strSQL = strSQL & "SickPersonalTime = "
strSQL = strSQL & intNewPersonal & " "
strSQL = strSQL & "WHERE EmployeeID = "
strSQL = strSQL & Me.cboEmployee.Value

Would it be more appropriate to move "me.cboEmployee.value" into a
variable, and then use the variable in the SQL statement? Or does it
even matter? I just want to be aware of any problems that might
occur. I have several SQL statements that I build this way, and I
would just like some input.

Thanks in advance for your reply,
Andrea

[DFS]

"Andrea" <cl****@healingamerica.com> wrote in message
news:17*************************@posting.google.co m...

I'm currently creating an Access 2000 database to keep track of
employee vacation hours. I have a form in which the number of hours
requested off by an employee is entered into a text box. Then the
employee's remaining vacation hours are updated through a SQL update
statement. I build the SQL statement through VBA and then execute it.

Here is what my SQL looks like:
strSQL = "UPDATE Employee SET VacationTime = "
strSQL = strSQL & intNewVacation & ", "
strSQL = strSQL & "SickPersonalTime = "
strSQL = strSQL & intNewPersonal & " "
strSQL = strSQL & "WHERE EmployeeID = "
strSQL = strSQL & Me.cboEmployee.Value

Would it be more appropriate to move "me.cboEmployee.value" into a
variable, and then use the variable in the SQL statement? Or does it
even matter? I just want to be aware of any problems that might
occur. I have several SQL statements that I build this way, and I
would just like some input.
Andrea,

It doesn't really matter. Your form reference is fine (you don't need the
..Value part if you format the form control correctly). A variable would
work too, and would be better - but not required - if you moved your code to
a module outside the form:

Public Sub updateEmployeeData(employeeID as integer, vacationHrs as integer,
sickTime as integer)
strSQL = "UPDATE Employee
strSQL = strSQL & "SET VacationTime = " & vacationHrs & ", "
strSQL = strSQL & "SickPersonalTime = " & sickTime & " "
strSQL = strSQL & "WHERE EmployeeID = " & employeeID & ";"
End Sub

Thanks in advance for your reply,
Andrea

[Mike Storr]

Using a direct reference to a value is usually prefered over stored ones.
There's nothing wrong with how you've used it here.
(Provided the syntax is correct)

Mike Storr
www.veraccess.com

"Andrea" <cl****@healingamerica.com> wrote in message
news:17*************************@posting.google.co m...

I'm currently creating an Access 2000 database to keep track of
employee vacation hours. I have a form in which the number of hours
requested off by an employee is entered into a text box. Then the
employee's remaining vacation hours are updated through a SQL update
statement. I build the SQL statement through VBA and then execute it.

Here is what my SQL looks like:
strSQL = "UPDATE Employee SET VacationTime = "
strSQL = strSQL & intNewVacation & ", "
strSQL = strSQL & "SickPersonalTime = "
strSQL = strSQL & intNewPersonal & " "
strSQL = strSQL & "WHERE EmployeeID = "
strSQL = strSQL & Me.cboEmployee.Value

Would it be more appropriate to move "me.cboEmployee.value" into a
variable, and then use the variable in the SQL statement? Or does it
even matter? I just want to be aware of any problems that might
occur. I have several SQL statements that I build this way, and I
would just like some input.

Thanks in advance for your reply,
Andrea

[Andrea]

Thank you for your replies, I just needed to know that I was on the
right track. I appreciate your input.

Thanks again!
Andrea

[Pink Panther]

Hi Andrea,

Just a suggestion -

Using line continuation characters makes your code more readable and
it's quicker to type too.

strSQL = "SELECT field1, field2 " & _
"FROM tbl1 " & _
"WHERE field2 = 'x' " & _
"ORDER BY field1"

I currently work on a database with lots of code using the method you
posted (the guy never moved beyond Access 2 code)

eg. strSQL = strSQL & "FROM tbl1 "
strSQL = strSQL & "WHERE etc "

and it ain't pretty.

Hope you don't mind my suggestion and it helps your code readability.

Regards,

Peter

cl****@healingamerica.com (Andrea) wrote in message news:<17**************************@posting.google. com>...

Thank you for your replies, I just needed to know that I was on the
right track. I appreciate your input.

Thanks again!
Andrea

[Pink Panther]

Hi Mike,

Using a direct reference to a value is usually prefered over stored ones.
Depends how many times you use it, doesn't it?

In a loop it would be quick to use a variable than reference a control n times.

Of course in this case it's an extra line of unneeded code.

Regards,

Peter

"Mike Storr" <st******@sympatico.ca> wrote in message news:<7V**********************@news20.bellglobal.c om>... Using a direct reference to a value is usually prefered over stored ones.
There's nothing wrong with how you've used it here.
(Provided the syntax is correct)

Mike Storr
www.veraccess.com