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.
thandelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can do that with one query:

Update and Append Records with One Query

/gustav
0
ste5anSenior DeveloperCommented:
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")) & ");"
.RunSQL sSQL

Public Function SqlDateJet(ADate As Variant) As String

  On Local Error GoTo LocalError
  
  SqlDateJet = Format(ADate, "\#m\/d\/yyyy#")
  Exit Function
  
LocalError:
  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

0
thandelAuthor Commented:
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)
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PatHartmanCommented:
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.
0
thandelAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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.

/gustav
0
thandelAuthor Commented:
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.
0
PatHartmanCommented:
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.
1
thandelAuthor Commented:
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.
0
Gustav BrockCIOCommented:
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.
Else
    sSQL = sSqlAppend    ' Your append SQL.
End If
CurrentDb.Execute sSQL

Open in new window

/gustav
0
thandelAuthor Commented:
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.
0
ste5anSenior DeveloperCommented:
Using my functions, e.g.

Public Sub Test()

  Dim ComputerName  As String
  Dim ID As Long
  Dim Office  As String
  Dim Sql As String
  
  ComputerName = Environ$("computername")
  'ID = Me.ID
  'Office = "some Value"
  Sql = _
    "UPDATE tEmailRx " & _
    "SET Office = " & SqlQuote(Office) & ", " & _
    "Ws = " & SqlQuote(ComputerName) & " " & _
    "WHERE MR = " & ID & ";"
  CurrentDbC.Execute Sql
  If CurrentDbC.RecordsAffected = 0 Then
    Sql = _
      "INSERT INTO tEmailRx (CreateDate, MR, RxType, Office, Ws) " & _
      "VALUES (" & SqlDateJet(Date) & ", " & ID & ", 'CL', " & SqlQuote(Office) & ", " & SqlQuote(ComputerName) & ");"
    CurrentDbC.Execute Sql
  End If

End Sub

Open in new window


p.s. the service is bringing people together. But we help on a voluntarily basis.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thandelAuthor Commented:
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.
0
PatHartmanCommented:
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.
0
ste5anSenior DeveloperCommented:
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.
0
Gustav BrockCIOCommented:
For inserting today's date, all you need is Date():

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

Open in new window


/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.