Solved

Data Type Mismatch using Calendar Control

Posted on 2009-04-01
12
295 Views
Last Modified: 2012-05-06
I am stumped on something that should be very simple...

I have a form with a calendar control.  As a new date is selected in the calendar control, I am storing the value in a temporary table so that I can allow the user to select multiple values.  The TEMP table field is a Date data type.

Before a new value is inserted, I'm first performing a check to be sure that the value wasn't already selected.  BUT, I continue to get a Type Mismatch error.

I've tried MANY different ways, but none have worked and I am completely stumped.

Temp Table:
   Tablename: tbl_TEMP_TrainingDates
   Column: TrainingDate (Date/Time datatype)

Form Calendar Control Name: TrainingCal

See VBA code below, the error occurs in the recordset string.
See notes at the bottom of the VBA code for other variations I have tried.

Dim strValDates As Recordset
 
Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  
                              tbl_TEMP_TrainingDates WHERE TrainingDate = '" & _
                              Me.TrainingCal & "'")
 
'****************************
''ve tried the above code a dozen different ways
'--Surrounding the Me.TrainingCal with single quotes, "#" signs, both single quotes And "#" signs
'--Creating a Date variable and then assigned the TrainingCal value to this variable and then using that variable within the recordset.
'--Created a String variable and set it to the TrainingCal value
'--Tried using the CDate function to force the value to be a date

Open in new window

0
Comment
Question by:bkapla1
12 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24037734
Hi bkapla1,

WHERE TrainingDate = #" & Format(TrainingCal , "mm/dd/yy") & "# )


Good Luck!

Gary
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037739
A date comparison in SQL has to be wrapped with #'s not'

Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  
                              tbl_TEMP_TrainingDates WHERE TrainingDate = #" & _
                              Me.TrainingCal & "#")

Of Me.TrainingCal id not in the format of MM/DD/YYYY then you will need to format it.

Set strValDates = CurrentDb.OpenRecordset("SELECT TrainingDate FROM  
                              tbl_TEMP_TrainingDates WHERE TrainingDate = #" & _
                              Format(Me.TrainingCal,"MM/DD/YYYY") & "#")

Cheers, Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24037740
If a date field ,try wrapping in # instead of single quotes
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:bkapla1
ID: 24037771
Thanks to all, I've tried all of the above solutions already with no luck.

However, I just decided to force within the TEMP table itself that the field will not allow duplicates.  In my Insert statement in the VBA code, I am hiding the warnings, so this will eliminate the duplicates, but is not as ideal of a solution because if the user clicks on that date on the calendar control, essentially nothing happens, so they don't get any prompts that they attempted to enter a duplicate, but then again, it does provide a feasible solution.

I am just going to go with that because as you all have pointed out above, the code that I already have tried should have worked already, but for some reason continues to error.

Thanks anyhow!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037782
Is the issue with the SQL or getting the value from the control, you can try Me.TrainingCal.Value or Me.TrainingCal.text and confirm that this is actually a date.
Chers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037800
Hi you have just started the delete request as I was entering my previous post, can you please check this and confirm if the proble you are having is with the SQL or returning the value from the control.
Cheers, Andrew
0
 

Author Comment

by:bkapla1
ID: 24037803
Andrew,

It's with the SQL since it is stating type mismatch.

I've also tried using the "value" at the end of the Me.TrainingCal as well, but that doesn't work.

What is odd is that I took the exact SQL statement used in the recordset and had it inserted into a text box.  Then, I copied that value and made it into a query, ran the query, and it worked fine... perhaps it's a corruption issue??  Strange....
This version above worked when I surrounded the variable with # signs, but no quotes.

So, the fact that the exact statement worked as a query, but not within VBA in a recordset statement is quite odd.
0
 
LVL 28

Accepted Solution

by:
TextReport earned 500 total points
ID: 24037826
Oh dear!

Try changing your DIM

Dim strValDates As DAO.Recordset

Cheers, Andrew
0
 

Author Comment

by:bkapla1
ID: 24037839
Andrew, that was it!

AWESOME!!

Thanks for your help... I hope I can still award you points... let me try now.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037874
Isn't it strange that all the experts all went the same way (including myself) and we all missed the most obvious.
Just to confirm the problem is that RECORDSET exists in DAO as well as ADODB and if both references are selected it pick from the first one in the references list, for that reason you are advised to prefix the recordset as demonstrated.

Cheers, Andrew
0
 

Author Comment

by:bkapla1
ID: 24037881
Andrew,
 Thanks!  I never actually have used this prefix before (maybe because sometimes I forget to even DIM the variable for a recordset)...Thanks for the advice as I'll be sure to use that going forward.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 24037911
"maybe because sometimes I forget to even DIM the variable for a recordset" from now on I would recomend that you put OPTION EXPLICIT in your modules, this forces you to declare your variables but will save hours of pain when you mistype a variable name, the Option Explicit will error the mis typed variable providing of course you haven't mistyped it to another valid variable name.

The Option Explicit can be turned on in VBA for your new modules in the Option.

Cheers, Andrew
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

792 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