Prevent Duplicate date/times being posted for particular CustIDs

[exceljockey]

Hi All



I am new to Access (using 2003) so please be patient; I am having troubles with this particular problem.

At the moment, our form is set up with 4 fields (that you are interested in). Status_Date, Status_time, Cust_ID, Status.
Now when entereing this information, the table is then populated but the status_Date and Status_time are merged together into one column, so in the tbl_Main, you would see this status date as "dd/mm/yy hh:mm". If it helps, the current code for this submit is:


Set rs = db.OpenRecordset("tbl_Main")
With rs
.AddNew
.Fields("Status_Date") = Me.Status_Date & " " & Me.Status_time
..........


Now the problem is as follows: sometimes we have a problem where for a particular CustID, a duplicate date and time is posted (for example "01/01/13 09:00" is posted twice, for custID "A100")

I want to stop this from happening, so I am trying to write a line of code where im counting the amount of times this certain status date appears for the certain customer, and if this amount is >= 1, then exit the function and do not save the record.

I have tried using:

If IsNull(DLookup("Cust_ID", "tbl_Main", "Status_Date = #" & Me.Status_Date & "#")) = False Then Exit Function

This sometimes works, and sometimes doesnt - this is only for the date field being entered into the form and does not take time into consideration, so obviously Id need to somehow incorporate me.status_time into this as well.

I also tried using a SQL string:

dim strSQL1 as String

strSQL1 = "SELECT COUNT(Me.Status_Date) FROM tbl_Main" & "WHERE Cust_ID = " & Me.Cust_ID & ";"

If IsNull(strSQL1) = False Then Exit Function

But this does not work at all (I am very inexperienced with SQL so im not expecting it to work as it is).

Does anyone know how I could fix my problem?

Thanks,
EJ

[RuralGuy]

Is the [Status_Date] field a String (text) field or a DateTime field? I take it you are aware of the power and flexibility of a DateTime field: http://support.microsoft.com/kb/q130514/en-us

[exceljockey]

Hi, it is a date/time field.

[RuralGuy]

Is the [Status_time] control bound to a DateTime field as well. I guess I should determine if the [Status_Date] control is bound to the Status_Date field as well.

[exceljockey]

So the main table is composed of: Cust_ID, Status_Date (which is a merge of me.status_date and me.status_time from the form): this is a date/time fiel... and the last column is status.
On the form, the date and time are seperated into two seperate input boxes, but they merge together using the "db.OpenRecordset("tbl_Main").Fields("Status_Date" ) = Me.Status_Date & " " & Me.Status_time" line.

Does this answer your question? Or have I misunderstood?

[RuralGuy]

Have you tried:
If IsNull(DLookup("Cust_ID", "tbl_Main", "Status_Date = #" & Me.Status_Date & " " & Me.Status_time "#")) = False Then Exit Function

[exceljockey]

Yes I have tried this, it just bypasses this line no matter what date is used. If i then use <> False instead, it will exit functio no matter what date i use.

Its really beginning to really frustrate me...

[orange]

It might be helpful to readers if you were to describe briefly what you database is about. Can you tell us about your tables? Some sample data identifying the input and the output you would like to have would be useful. You do realize that a Date/Time field can have date and time info in that 1 field?

You should check your table to see if that Customer and DateTime are present - to prevent duplicates. I would suggest whenever you add a record you always check, either via code or referential integrity rules, to avoid creation of duplicates.

[RuralGuy]

Dim a DateTime variable and set it to the value you want to test for, Do a MsgBox on the variable before you run the DLookup and see if you have what you want. Then use the variable in the DLookup instead of the controls.

[exceljockey]

It might be helpful to readers if you were to describe briefly what you database is about. Can you tell us about your tables? Some sample data identifying the input and the output you would like to have would be useful. You do realize that a Date/Time field can have date and time info in that 1 field?.

My database is to log and track case flow for particular customers, where the cases are in any given time (ie which team a case (or customer) is in at the moment, within the process that we are working on).
So the table consists of headers:

ID (which is an autonumber given to new data records), Cust ID (customer ID), Status (which is the status entered by the data entry teams, such as "Team B - Case received" or "Team B - Sent to Team C"), Status Date (which is a Date/Time field, such as "01/01/13 09:00"), and some further columns which I dont really need to talk about.

The form consists of the following combo boxes: Cust ID, Status, Status Date, Status Time (please note that status date and status time are split into two different boxes; reason being is that we wanted a calender pop up for the status date, and therefore needed to split these up. time needs to be recorded in order to track case flow better, if cases make it through different stages in the process on the same day, so that they are ordered correctly, such as Team A to Team B, Team B to Team C, etc).

I am currently having huge issues with this, am finding it extremely difficul to stop duplicate date and times being pushed through. I have tried using SELECT COUNT() WHERE, etc to return counts on dates and stopping it if the count gives me > 0, but this doesnt work (perhaps I am writing the SQL incorrectly:

If "SELECT COUNT(Status_Date) FROM tbl_Main WHERE Cust_ID =" & Me.Cust_ID & " AND Status_Date = " & dtNewDate1 & ";" <> 0 Then Exit Function

Where dtNewDate1 = Format(Me.Status_Date, "dd/mm/yy").

This doesnt work at all, it just exits function every time, even if the status date is unique to the particular cust ID.

Any Idea how I would word the Dlookup correctly, to look for the particular Cust ID (me.Cust_ID) within the main table, and see if the date tpyed into the combo box (me.Status_Date) exists?


Also To reply to RuralGuy: Cannot Dim DateTime in VBA? I believe this is in VB .net only?

[exceljockey]

I solved the issue.

Not the method i wanted originally, but it does the job. I added an extra column to the main table which is a DOUBLE variable in vba, date and time joined together into integer form.
I then refered to this when doing the DLOOKUP.

Problem Solved! Thanks those that tried to help, much appreciated!

[RuralGuy]

Glad to hear you resolved your issue. To set the record straight:

Also To reply to RuralGuy: Cannot Dim DateTime in VBA? I believe this is in VB .net only?

Not true. Works in VBA as well.