Solved

access 2007 vba Dcount() new problem

Posted on 2010-11-08
7
505 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

919 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

15 Experts available now in Live!

Get 1:1 Help Now