Update if field not null

S

Sash

I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
 
D

Damian S

Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
 
S

Sash

Very helpful and I will rate as such.

Can you please explain to me the quotes and single quotes in Access? I
totally agree with the double quote from hell comment, but it worked. As
long as I've been stumbling through this, I've never understood how Access
wants the quotes.

Thanks again!
Sash

Damian S said:
Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
Sash said:
I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
Click to expand...
Click to expand...
 
D

Damian S

Hi Sash,

Generally when you are building an SQL string, you use the double quotes at
the start and end of the string, and if you need to delimit text inside the
double quotes, use single quotes. That way you don't need to try to do the
""""" to end up with double quotes inside other double quotes... eg:

strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

It's much simpler to read that way I find.

D.

Sash said:
Very helpful and I will rate as such.

Can you please explain to me the quotes and single quotes in Access? I
totally agree with the double quote from hell comment, but it worked. As
long as I've been stumbling through this, I've never understood how Access
wants the quotes.

Thanks again!
Sash

Damian S said:
Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
Sash said:
I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
Click to expand...
Click to expand...
Click to expand...
 
S

Sash

Sorry to be so brain dead, but it's been a long day. I just want to be sure
that I understand the quotes....again, this has always been an issue for me.
I do some crazy amazing stuff in access, but think I just get by on certain
stuff.....like this.....

Full line of Code:
strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

Understanding this element:
'" & me.TEXT1 & "'
'(i'm a value) "(text) &(concatinates) information.....clos it all by the
same

Understanding this element:
12 --- need nothing because it's a real value


Damian S said:
Hi Sash,

Generally when you are building an SQL string, you use the double quotes at
the start and end of the string, and if you need to delimit text inside the
double quotes, use single quotes. That way you don't need to try to do the
""""" to end up with double quotes inside other double quotes... eg:

strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

It's much simpler to read that way I find.

D.

Sash said:
Very helpful and I will rate as such.

Can you please explain to me the quotes and single quotes in Access? I
totally agree with the double quote from hell comment, but it worked. As
long as I've been stumbling through this, I've never understood how Access
wants the quotes.

Thanks again!
Sash

Damian S said:
Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
:

I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
Click to expand...
Click to expand...
Click to expand...
 
D

Damian S

No worries... your understanding of my single quotes explanation is spot on.

One last thing - if you think that you might have data with an apostrophe in
it, eg: the word I'm, you might want to check for them and replace them with
two single quotes... like this:

replace(VARIABLE, "'", "''")

that's double quote single quote double quote, double quote 2 single quotes
double quote.

This will replace the single quote in I'm with two of them I''m, which will
sort it out in your SQL string. This is only necessary when building an SQL
String - not if you are updating a recordset using DAO etc.

Damian.

Sash said:
Sorry to be so brain dead, but it's been a long day. I just want to be sure
that I understand the quotes....again, this has always been an issue for me.
I do some crazy amazing stuff in access, but think I just get by on certain
stuff.....like this.....

Full line of Code:
strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

Understanding this element:
'" & me.TEXT1 & "'
'(i'm a value) "(text) &(concatinates) information.....clos it all by the
same

Understanding this element:
12 --- need nothing because it's a real value


Damian S said:
Hi Sash,

Generally when you are building an SQL string, you use the double quotes at
the start and end of the string, and if you need to delimit text inside the
double quotes, use single quotes. That way you don't need to try to do the
""""" to end up with double quotes inside other double quotes... eg:

strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

It's much simpler to read that way I find.

D.

Sash said:
Very helpful and I will rate as such.

Can you please explain to me the quotes and single quotes in Access? I
totally agree with the double quote from hell comment, but it worked. As
long as I've been stumbling through this, I've never understood how Access
wants the quotes.

Thanks again!
Sash

:

Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
:

I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
Click to expand...
Click to expand...
Click to expand...
 
S

Sash

OKay. Thanks for your patience and not to say that I won't have more
questions, but think this points me in the right direction!!!!

Thank you,
Sash



Damian S said:
No worries... your understanding of my single quotes explanation is spot on.

One last thing - if you think that you might have data with an apostrophe in
it, eg: the word I'm, you might want to check for them and replace them with
two single quotes... like this:

replace(VARIABLE, "'", "''")

that's double quote single quote double quote, double quote 2 single quotes
double quote.

This will replace the single quote in I'm with two of them I''m, which will
sort it out in your SQL string. This is only necessary when building an SQL
String - not if you are updating a recordset using DAO etc.

Damian.

Sash said:
Sorry to be so brain dead, but it's been a long day. I just want to be sure
that I understand the quotes....again, this has always been an issue for me.
I do some crazy amazing stuff in access, but think I just get by on certain
stuff.....like this.....

Full line of Code:
strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

Understanding this element:
'" & me.TEXT1 & "'
'(i'm a value) "(text) &(concatinates) information.....clos it all by the
same

Understanding this element:
12 --- need nothing because it's a real value


Damian S said:
Hi Sash,

Generally when you are building an SQL string, you use the double quotes at
the start and end of the string, and if you need to delimit text inside the
double quotes, use single quotes. That way you don't need to try to do the
""""" to end up with double quotes inside other double quotes... eg:

strSQL = "insert into TABLE (FIELD1, FIELD2) values ('" & me.TEXT1 & "', 12)"

It's much simpler to read that way I find.

D.

:

Very helpful and I will rate as such.

Can you please explain to me the quotes and single quotes in Access? I
totally agree with the double quote from hell comment, but it worked. As
long as I've been stumbling through this, I've never understood how Access
wants the quotes.

Thanks again!
Sash

:

Hi Sash,

The first problem with your logic is that using elseif's means that only ONE
item will ever be updated - the first one that you come to. Split them into
single if statements, with an endif afterwards. Also, get rid of the
horrible double quote hell and replace with single quotes as your text
identifier like this:

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = '" & Trim$(Me.[anesth1]) &
"', "
end if

If Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = "'" &
format(Me.[operation_start_date], "yyyy/mm/dd") & "',"

end if

Also, you will see that I have formatted the date above - this will help you
ensure that the date isn't transposed eg: 6/12/2006 won't become 12/6/2006.

Hope that helps.

Damian.
:

I'm trying to create a SQL statement by going through each field, verify if
it's null and then update if it is NOT null. If someone can look at my
thought process and logic. I'm writing to a SQL Server DB.



Below is the first two lines of code to give you an idea of my logic. I do
this for 20 fields ....

strSQL = "UPDATE dbo_Anesth SET "
If Not IsNull(Me.[anesth1]) Then
strSQL = strSQL & "dbo_Anesth.[ip_anesth1] = """ & Trim$(Me.[anesth1]) &
""","
ElseIf Not IsNull(Me.[operation_start_date]) Then
strSQL = strSQL & "dbo_Anesth.[ip_opdate] = """ &
Me.[operation_start_date] & ""","


And end with the following because I know that i will ALWAYS have pat-mrn.


ElseIf Not IsNull(Trim$(Me.[pat-mrn])) Then
strSQL = strSQL & "dbo_Anesth.[ip_ptmrn] = """ & Trim$(Me.[pat-mrn]) &
""" "
End If

strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"
Click to expand...
Click to expand...
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

Subform then Write SQL 5
VBA works in orignal but fails in copy of database 1
Slow Code/Query Execution 9
Text feild check for pattern or if last 5 are numeric? 1
"select case" 2
help with a VBA 4
Error 3075 Missing Operator 7
Second Report not printing - whats wrong with the VBA I have?????? 1

Top