Solved

Data Type Mismatch using Calendar Control

Posted on 2009-04-01
12
292 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
Comment Utility
Hi bkapla1,

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


Good Luck!

Gary
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
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
Comment Utility
If a date field ,try wrapping in # instead of single quotes
0
 

Author Comment

by:bkapla1
Comment Utility
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
Comment Utility
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
Comment Utility
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
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)

 

Author Comment

by:bkapla1
Comment Utility
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
Comment Utility
Oh dear!

Try changing your DIM

Dim strValDates As DAO.Recordset

Cheers, Andrew
0
 

Author Comment

by:bkapla1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

763 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