Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Question Unanswered: How do I check to see if a date, entered on a form, is already in the table

    Hello and Thank you in advance.

    I have a database that we use to pull data from an AIX machine everyday.
    I can do it without making duplicates.
    The users we have tried in the past, like to pull the same day 4 or 5 times.
    That's the problem.
    The data is sales data for cigarettes. Shows how many packs of each brand/type of cigarettes were purchased at each location for each day.
    When you pull the same data 4 or 5 times that tends to through off the count.

    The form has a date field called DateToPull.
    I need to check to see if that date has already been pulled into the RAW table.
    The date field in the RAW table is PSDate.
    If the date is not in the table, then run the macros for pulling and processing the data.
    If its not, then tell them to try again.

    Generally each pull of data will create around 6-7k of records so there should always be something in there to check against.

    I know what I want to do, but just have no clue on how to write it.
    My coworker and I have spent to many hours searching and trying useless leads.
    Now we need to get this done.
    Help?!?!?

    In a nutshell.
    If [form1]![DateToPull] is not in [RAW]![PSDate] Then
    do these access macros to pull and process the data
    Else
    tell the user, the data has already been pulled for that day. try again.
    EndIf

    It would be nice if it was that easy. lol
    Then again if it was that easy the user(s) would never create dups.

    Forgot to mention we are using Access 2000

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    You can either use a DLookup or open a recordset where DateToPull = PSDate and check the record count.

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Well... therein lies the problem...
    I know what you are talking about but I have no clue on how to code it.
    I've been busy with Monday and will take a look tomorrow and see what I can find on the forums.
    Thank you

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    If Nz(DLookUp("PSDate", "Raw", "PSDate = #" & [form1]![DateToPull] & "#"), -1) = True Then
        ' Process data
    Else
        ' Message to user
    End If
    You could have to experiment a little bit with the formating of the date, depending on how it's stored in the table ([PSDate]) and how it's handled in the form control ([DateToPull]).

    You could also have to properly format or cast both dates is they are stored in full Date/Time format (10/30/2010 08:19:15 does not equates 10/30/2010 08:19:21).
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Posts
    3
    Thank you!!
    After a couple of tries I got it to work.
    My Access was having problems with the reference to the form.
    Below is what I ended up with.
    Thank you again!!!

    If Nz(DLookup("PSDate", "RAW", "PSDate = #" & Forms![Form1]![DateToPull] & "#"), -1) = True Then
    ' Process data
    Else
    ' Message to user
    End If

    BTW, The date in the table and the form is just a date, no time.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •