Data Mismatch Error For Date Field

[jimmy_bones]

Any idea what the problem here is? I keep getting a Data type miismatch error with this line:

"WHERE [Node Name] = '" & [Node Name].Value & "'" And "[Record Creation date] = '" & maxDate & "'" & _

The date part is causing the problem. I tried it like this too:




"[Record Creation date] = #" & maxDate & "#'





The maxDate variable is type Date. THe [Record Creatoin Date] field is dateTime.

[RuralGuy]

Date and DateTime type fields are the same and YES the delimitor for dates is the octothorpe (#) and not a tick (') or quote (").

[ssanfu]

You also have quotes in the wrong place:
You have:

"WHERE [Node Name] = '" & [Node Name].Value & "'" And "[Record Creation date] = '" & maxDate & "'" & _

should be:

"WHERE [Node Name] = '" & [Node Name] & "' And [Record Creation date] = #" & maxDate & "#" &

" .VALUE" is the default property and is not required (but doesn't hurt)

Also note that you shouldn't use spaces in object names. See
http://access.mvps.org/access/tencommandments.htm

[jimmy_bones]

Thanks. That seemed to solve the first problem but here's another. As in the code above, I'm using the max date from field [Record Creation Date] as part of the criteria to populate a form. Here is a sample date as it would appear in the database:

2012-09-04 17:39:30.270

But when I use this code to get the max date:

maxDate = DMax("[Record Creation Date]", "[dbo_Node Opt Birth Certificate]", "[Node Name] = '" & daNode & "' ")

the end result looks like this:

9/4/2012 5:39:30 PM

So when I run this code to open and populate the new form:

DoCmd.OpenForm "Node Opt Birth Certificate - Field", , , "[Node Name] = '" & Me.[Node Name].Value & "' AND [Record Creation Date] = #" & maxDate & "#"

I get nothing. I assume this is because the VB doesn't consider these to be matching values because of the different formats. Is there a simple way to reformat one or the other so they're compatible?

[RuralGuy]

Perhaps this link will supply a better understanding of the DateTime field: http://support.microsoft.com/kb/q130514/en-us

[jimmy_bones]

I ended up trying this. The SQL works fine in SQL Server but I get an error message about the "convert" function when I try to run it in VBA via Docmd.RunSQL:

DoCmd.RunSQL "UPDATE [dbo_Node Opt Birth Certificate] SET [Record Creation Date VBA Format] = left(convert(varchar,[record creation date],109),LEN(convert(varchar,[record creation date],109))-6)+' '+right(convert(varchar,[record creation date],109),2) WHERE [Record Creation Date VBA Format] is NULL"

[RuralGuy]

I don't believe VBA includes the "Convert" instruction and since I don't know what it does in SQL I'm at a loss to assist at this point. I would guess that the [Record Creation Date VBA Format] field appears to be a string field though.

[ssanfu]

Will there be records that match down to the second?

It looks like this is a date/time from SQL Server : 2012-09-04 17:39:30.270 ?? Access chokes on the ".270" part. In Access you can convert the date/time to just the date by using the Int() function.

maxDate = DMax("[Record Creation Date]", "[dbo_Node Opt Birth Certificate]", "[Node Name] = '" & daNode & "'")
maxDate = maxDate = Int(maxDate)

You might try: (untested)

maxDate = DMax("[Record Creation Date]", "[dbo_Node Opt Birth Certificate]", "[Node Name] = '" & daNode & "'")
'convert to date only
maxDate = Int(maxDate)

DoCmd.OpenForm "Node Opt Birth Certificate - Field", , , "[Node Name] = '" & Me.[Node Name].Value & "' AND Int([Record Creation Date]) = #" & maxDate & "#"