Update if field not null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sash - 09 Nov 2006 00:26 GMT

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 & """;"

Reply to this Message

Damian S - 09 Nov 2006 00:36 GMT

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
[quoted text clipped - 19 lines]
>
> strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message

Sash - 09 Nov 2006 00:41 GMT

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,
>
[quoted text clipped - 46 lines]
> >
> > strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message

Damian S - 09 Nov 2006 01:04 GMT

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.
>
[quoted text clipped - 56 lines]
> > >
> > > strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message

Sash - 09 Nov 2006 02:38 GMT

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

> Hi Sash,
>
[quoted text clipped - 69 lines]
> > > >
> > > > strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message

Damian S - 09 Nov 2006 03:39 GMT

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.

> 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.
[quoted text clipped - 85 lines]
> > > > >
> > > > > strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message

Sash - 09 Nov 2006 03:56 GMT

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

> No worries... your understanding of my single quotes explanation is spot on.
>
[quoted text clipped - 102 lines]
> > > > > >
> > > > > > strSQL = strSQL & "WHERE dbo_Anesth.[fldpatacct] = """ & stptacct & """;"

Reply to this Message