Link to home
Start Free TrialLog in
Avatar of thandel

asked on

MS Access SQL Syntax Update / Insert

I have MS Access 2003 table that currently I am insert data for a given even on a given date.  I'm using the following code successfully:

    sSQL = "INSERT INTO tEmailRx (CreateDate, MR, RxType, Office, Ws)"
    sSQL = sSQL & " VALUES (#" & Date & "#, " & Me.ID & ",'CL', '" & sOffice & "','" & Environ$("computername") & "')"
    DoCmd.RunSQL sSQL

How I'm finding that for the same "MR" there might be more than once entry on the same date.  I would like to update the SQL to find if a MR already exists for today (XX/XX/XX) and if so instead inserting another row update the existing entry's Rx type

The table structure is:
CreateDate = Date/Time
MR = Long Int
RXType = String
Office = Text
Ws = Text

I thought the first step was to determine if an existing MR with today's date existed.... so I have this which does return the number of finds

inumtrays = DCount("*", "tEmailRx", "MR = " & Me.tMRN And "DATE = Now()")

But now just not sure how to implement it as it would be the exiting code if inumtrays = 0 then if > 0 then some new "update" code.

Any help is appreciated.
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

You can do that with one query:

Update and Append Records with One Query

First of all: You were lucky. Cause string concatenation has some pitfalls. Consider a different UI format for dates, Access accepts only US, or strings containing single or double quotes as content.

Use either Format() and Replace() your self, use some functions like

sSQL = _
  "INSERT INTO tEmailRx (CreateDate, MR, RxType, Office, Ws) " & _
  "VALUES (" & SqlDateJet(Date) & ", " & Me.ID & ",'CL', " & SqlQuote(sOffice) & "," & SqlQuote(Environ$("computername")) & ");"

Public Function SqlDateJet(ADate As Variant) As String

  On Local Error GoTo LocalError
  SqlDateJet = Format(ADate, "\#m\/d\/yyyy#")
  Exit Function
  SqlDateJet = Format(Now, "\#m\/d\/yyyy#")
End Function

Public Function SqlQuote(AString As String, Optional ADelimiter As String = "'") As String

  SqlQuote = ADelimiter & Replace(AString, ADelimiter, ADelimiter & ADelimiter) & ADelimiter

End Function

Open in new window

or Gustav's CSql().

Then read about using Environ(). Values can be easily spoofed. See here for a solution.

Use CurrentDbC.Execute and the RecordsAffected return value with an UPDATE. When the UPDATE returns 0 affected rows then INSERT your row instead.

Option Compare Database
Option Explicit

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

  If m_CurrentDb Is Nothing Then
    Set m_CurrentDb = CurrentDb
  End If

  Set CurrentDbC = m_CurrentDb

End Property

Open in new window

Avatar of thandel


Ok thanks I've got a compile error: ByRef argument type mismatch (on sOffice)

Doesn't your solution always add another row?   I was hoping to update and existing entry for the same MR with the same date (today)
Call me crazy but I would try the query link posted by Gus before I embarked on writing code.  As it happens, I've used this query technique many times but be aware that it is a Jet/ACE "trick" and won't work in SQL Server.
Avatar of thandel


not that I don't appreciate the info but I think this is over kill.  I just need help with a SQL syntax for updating an existing row based on 2 criteria points MR and date.
First you need to fix the concatenating of the values.
Then, I can't see the overkill adjusting one query instead of running first a search, then either of two queries.

But, if you insist, the easy method is to use the query wizard to create the query.

Avatar of thandel


OK thank you but the initial question was for SQL help not via the query wizard.  this is for a small database for home use... so we don't need to worry about international values or spoofing.  Just need help with a single SQL line for updating a table if an existing row is present based on a date and MR number match.  Again not tying to be difficult here and I do appreciate your services.
All the query wizard does is to help you build valid SQL.  You can switch from QBE view to SQL view to copy the SQL string.

Typically when people ask for SQL help, it is because they are not familiar with Access SQL syntax.  Teaching them how to use the QBE gives them a huge leg up on their journey.   QBE builds the SQL string for you so I use it all the time.  Why should I type a select string and joins when I can build them with a few clicks.  Usually I use the querydef because using embedded SQL has no advantage but if you prefer to embed the SQL have at it.  Documentation tools do not document embedded SQL but they do document Querydefs  Querydefs are also easier to reuse.
Avatar of thandel


Thank you but while familiar with the query wizard I am not sure how to incorporate that along with proper quotes and single quotes of a SQL string.  I have asked for assistant on this site in the past for SQL assistance with great success.  If you are not able to assist then I am willing to wait for an expert to provide a solution to my question.

Thank you.
What is your problem actually? Either use the simple method in my link above with one query, or look up an existing record and use either of two queries:

If IsNull(DLookup("[MR]", "tEmailRx", "[MR] = " & Me.tMRN.Value & " And [DATE] = Date()")) Then
    sSQL = sSqlInsert    ' Your insert SQL.
    sSQL = sSqlAppend    ' Your append SQL.
End If
CurrentDb.Execute sSQL

Open in new window

Avatar of thandel


Thanks no problem, I am paying for a service and am looking for help.

Your solution provides me with a solution I've listed with my initial question.  The issue I'm having is not with an IF statement but with the SQL statement.  I listed the append SQL code that is working... the issue I'm having is with the  append/update SQL command.  Can you elaborate on the update/append command?  I've got the insert no problem but can't get the syntax correct for the update.

Thank you for your help.
Avatar of ste5an
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of thandel


Thanks let me try it again, but why can't the need for the functions for a date, why can't we just use a date why the need for a function?  The user has zero input on the date, it all based on an actual date.
Gus did not suggest a function.  Did you try to follow the directions to build the query using QBE?  Once the string is built, you can switch to SQL view and modify it if you don't want to use it as a querydef.  The reason the QBE was suggested is because it allows YOU to solve your own problem.  If you want someone to write the query for you so that you don't need to do anything, you might try a gig.
You need to format the date, cause in a SQL string it must have the US date format. The format used when using Date() without format depends on the users regional settings. As you don't control these settings, the outcome is arbitrary. Thus it can break your application.
For inserting today's date, all you need is Date():

sSQL = sSQL & " VALUES (Date(), " & Me.ID & ",'CL', '" & sOffice & "','" & Environ$("computername") & "')"

Open in new window