Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Data Type Mismatch using Calendar Control

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
bkapla1
Asked:
bkapla1
1 Solution
 
tbsgadiCommented:
Hi bkapla1,

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


Good Luck!

Gary
0
 
TextReportCommented:
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
 
rockiroadsCommented:
If a date field ,try wrapping in # instead of single quotes
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bkapla1Author Commented:
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
 
TextReportCommented:
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
 
TextReportCommented:
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
 
bkapla1Author Commented:
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
 
TextReportCommented:
Oh dear!

Try changing your DIM

Dim strValDates As DAO.Recordset

Cheers, Andrew
0
 
bkapla1Author Commented:
Andrew, that was it!

AWESOME!!

Thanks for your help... I hope I can still award you points... let me try now.
0
 
TextReportCommented:
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
 
bkapla1Author Commented:
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
 
TextReportCommented:
"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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now