Solved

access 2007 vba Dcount() new problem

Posted on 2010-11-08
7
494 Views
Last Modified: 2012-05-10
The code:

If DCount("[Part_Number]", "Etching_Schedule", "[Part_Number] = '" & Me.txtPart_Number & "'" And "[Run_Date] = #" & Me.dteRun_Date & "#") > 0 Then
            MsgBox "That part number already exists for that date."
End If

It fails with runtime error 13 data mismatch.  There is something about the:
And "[Run_Date] = #" & Me.dteRun_Date & "#"

The Run_Date field is a date/time field but only the date is saved.

Thanks,
Brooks
0
Comment
Question by:gbnorton
  • 3
  • 2
  • 2
7 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34086556


try this

If DCount("[Part_Number]", "Etching_Schedule", "[Part_Number] = '" & Me.txtPart_Number & "' And [Run_Date] = #" & Me.dteRun_Date & "#") > 0 Then
            MsgBox "That part number already exists for that date."
End If
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34086563
is the field [Run_Date] DateTime data type?
0
 

Author Comment

by:gbnorton
ID: 34086598
Yes.  It is DateTime data type
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34086618
ok, try the codes i posted above..
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34086637
try using format or datevalue and wrap your field with cdate in case its been defined as a text field
plus split it up to make it more readable

eg

    Dim sWhere As String
   
    sWhere = "[Part_Number] = '" & Me.txtPart_Number & "' AND DateValue([Run_Date]) = #" & CDate(Me.dteRun_Date) & "#"

OR

    sWhere = "[Part_Number] = '" & Me.txtPart_Number & "' AND Format([Run_Date],'Short Date') = #" & CDate(Me.dteRun_Date) & "#"
   

    If DCount("[Part_Number]", "Etching_Schedule", sWhere) > 0 Then
        MsgBox "That part number already exists for that date."
    End If
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 34086653
If you just store the date and not the time, you can try without using datevalue or format, see if that makes a difference
eg

sWhere = "[Part_Number] = '" & Me.txtPart_Number & "' AND [Run_Date] = #" & CDate(Me.dteRun_Date) & "#"

so basically just wrap field with cdate
0
 

Author Closing Comment

by:gbnorton
ID: 34086660
It took me a minute to see the difference between the new code and mine... but I filnally did see it and it works.  Thanks
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now