troubleshooting Question

MS Access SQL Syntax Update / Insert

Avatar of thandel
thandel asked on
Microsoft OfficeMicrosoft AccessSQL
16 Comments1 Solution348 ViewsLast Modified:
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.
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 16 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 16 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros