Solved

access 2007 vba Dcount() new problem

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

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

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

Author Comment

by:gbnorton
ID: 34086598
Yes.  It is DateTime data type
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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…

730 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