VBA Compile Error: Variable not defined

D

Duncs

I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
 
S

Steve Schapel

Duncs,

Well, the basic problem is that you are putting the DateLocked field outside
of the SQL string, and thereby treating it as a variable, which it isnt.

But anyway, you are making it more complicated than it needs to be. Try it
like this...

"UPDATE tblActionDetails SET StatusId = " & stsWorkable & _
" WHERE StatusId = " & stsLocked & _
" AND Date() >= (DateLocked + 7)"
 
D

Duncs

Cheers Steve. That's my problem all the time...make things more
complicated than they need to be!!

Duncs
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 
R

Rudolf Lamour

Duncs said:
I'm trying to write a piece of SQL code for to be executed within a
VBA module. My SQL code is as follows:

"UPDATE tblActionDetails SET StatusId=" & stsWorkable & " WHERE
StatusId=" & stsLocked & " AND " & _
"#" & Format(Now(), "dd/mm/yyyy") & "# >= #" & DateAdd("d", 7, Format
(DateLocked, "dd/mm/yyyy")) & "#;"

Values are:

StatusID is a field in the table tblActionDetails
stsWorkable is a public enumerated set to 1
stsLocked is a public enumerated set to 2
DateLocked is a field in the table tblActionDetails

What I want to do is set the field StatusID to 1, where it has been
set to 2 for 7 or more days since the date value in DateLocked.

When I compile the code, it gives me the error:


"Variable not defined" and highlights the entry "DateLocked". What am
I doing wrong?

TIA

Duncs
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to use/create code in MS Word to calculate age? 0
VBA - exporting serial letters to single documents 0
Append Query Issues... 5
Query about an append query 1
Get date for start of week 3
VBA word change keys 0
Visio: copy text of a master shape to User-define Cells (Variable) 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0

Top