dcount code incorrect - please advise

[thewiseguy]

hi there -

i need to check that details of an order have not already been entered for the inputed IDNumber on the inputed OrderDate. in other words, i want to make sure that details of IDNumber 123456's order cannot be entered for 1/1/01 if an entry for that IDNumber with that date are already in tbl:OrderHistory.

the below code is not working and i'm not advanced enough to see what is wrong.

If (DCount("*", "tbl:OrderHistory", "[IDNumber] = " & Me.cmbIDNumber And _
    "[OrderDate] = #" & Me.txtOrderDate & "#") > 0) Then

thanks in advance...

 

[Jon K]

If IDNumber is a numeric field, you can use this:-

If (DCount("*", "tbl:OrderHistory", "[IDNumber] = " & Me.cmbIDNumber & _
   " And [OrderDate] = #" & Format(Me.txtOrderDate, "m/d/yyyy") & "#") > 0) Then

If IDNumber is a text field, you'll need to surround the ID value with a pair of single quotes:-

If (DCount("*", "tbl:OrderHistory", "[IDNumber] = '" & Me.cmbIDNumber & _
   "' And [OrderDate] = #" & Format(Me.txtOrderDate, "m/d/yyyy") & "#") > 0) Then

 

[KenHigg]

Are you getting an error message or is the if always excuting a true or a false part?

(I suspect you already know 'tbl:OrderHistory' is non standard naming convention...)

kh

 

[thewiseguy]

It's still not working - I get Run Time Error 13, Type mismatch.
I've looked to see I've spelt everything correctly and it all is.

IDNumber is a number field.

Yeh - i know it's the incorrect naming for the table but i'm taking over this db from some other perp so renaming everything would take a few days to follow through all the queries...

 

[KenHigg]

If (DCount("*", "tbl:OrderHistory", "[IDNumber] = " & Me.cmbIDNumber And _
"[OrderDate] = '#" & Me.txtOrderDate & "#'") > 0) Then

???
kh

 

[thewiseguy]

no luck.

it works when i use DCount on:

If (DCount("*", "tbl:OrderHistory", "[IDNumber] = " & Me.cmbIDNumber)> 0) Then

or:

If (DCount("*", "tbl:OrderHistory", "[OrderDate] = #" & _
Me.txtOrderDate & "#") > 0) Then

I just can't get the option of both

 

[KenHigg]

Code:
If (DCount("*", "tbl:OrderHistory","([IDNumber] = " & Me.cmbIDNumber & ") and ([OrderDate] = #" & Me.txtOrderDate & "#")) > 0) Then


???
kh

 

[thewiseguy]

no luck i'm afraid -
thanks for the ongoing help!

just tried leaving out the 'AND' and the error message showed that it was recognising the IDNumber and the OrderDate correctly to what I had entered as test values, but just missing an operator. So I put the 'AND' back in...

I bet it's something obvious or very devious...

anyone got any clues...?

 

[Jon K]

I have attached a sample database, which contains my previous code. It works on my system.


You can open the form, select the IDNumber 123456, enter a data e.g. 1/10/2004 and click on the command button to see if the database works on your system or not.

The code is in the On Click event of the command button:-

Private Sub cmdSearch_Click()
   If (DCount("*", "tbl:OrderHistory", "[IDNumber] = " & Me.cmbIDNumber & _
      " And [OrderDate] = #" & Format(Me.txtOrderDate, "m/d/yyyy") & "#") > 0) Then
   
      MsgBox "Sorry, the IDNumber and OrderDate already exist in the table."
   Else
      MsgBox "You can use these IDNumber and OrderDate."
   End If

End Sub

Note
In another thread, you posted some dates in the UK format:-

ID number Date Area Region
26784 14/2/03 Lower 2
37772 27/8/03 Upper 1
37772 1/3/04 Upper 3
50101 12/12/03 Lower 2
50101 4/2/04 Lower 2
50101 10/5/04 Lower 1


By default, the date delimiter (i.e. the # signs) treats the date as a US date in the format m/d/yyyy.

To use the # delimiter correctly on systems that use non-US date format, you need to use the Format function to convert the date to US format before you can delimit it with the # signs.
.

 

[thewiseguy]

cheers JonK -
i just figured it out as you sent me a post...

my " was after the AND instead of in front of it.
yeesh - i feel really embarrassed for such a silly mistake.
my only excuse is that i'm still learning and i can only learn from my mistakes.

as for the dates - the input fields are in dd/mm/yy format but the stored data in the table is in US format - i'm hoping that will be ok.

thank you