?
Solved

Type mismatch error on VB Where clause  (SQL building in a QRYSHELL)

Posted on 2006-05-17
11
Medium Priority
?
244 Views
Last Modified: 2010-04-17
i am getting a type mismatch on the following :

 strWHERE = strWHERE & " and b.date_ordered between " & "'" & Start_date & "'" And "'" & End_date & "'"

the start date and end date are text boxes that you will pop dates into (paremeters)
0
Comment
Question by:attachie
11 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 16702831
Hello attachie,

if this is Access VB then try replacing the ' single quote for the dates to #
strWHERE = strWHERE & " and b.date_ordered between " & "#" & Start_date & "# " And " #" & End_date & "#"

if that was not the solution then there is a space missing in your line
strWHERE = strWHERE & " and b.date_ordered between " & "'" & Start_date & "' " And " '" & End_date & "'"




hope this helps a bit
bruintje
0
 
LVL 3

Expert Comment

by:trainsdse
ID: 16702878
Try this

 strWHERE = strWHERE & " and b.date_ordered between " & "'" & Start_date & "' And '" & End_date & "'"
0
 

Author Comment

by:attachie
ID: 16703011
trainsdse:

thats giving me a weird error, that i have neevr seen  "you canceled the previous operation"

have you ever seen that before ,

and no i didnt cancel anything ...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:attachie
ID: 16703085
i tired your bruin,

however both didnt work
0
 

Author Comment

by:attachie
ID: 16703095
trainsdse:

the erro is pointing to

 DoCmd.OpenQuery "qryshell", acNormal, acEdit

this command
0
 

Author Comment

by:attachie
ID: 16703478
is there a step i need to take to enter a date paremeter in the text boxes and for it to pull the data for that date range
0
 
LVL 3

Accepted Solution

by:
trainsdse earned 200 total points
ID: 16703972
So this IS an Access Database, ok, in that case bruintje was logically right, just synatactually incorrect.

strWHERE = strWHERE & " and b.date_ordered between #" & Start_date & "# And #" & End_date & "#"

Now we are both assuming that Start_date is the name of a variable.  Is this a textbox name?
Also, show me the code where you actually use the strWhere variable and append it to the rest of the query.

How are you executing this string query?  I'd like to see that as well.
0
 
LVL 12

Expert Comment

by:vb_jonas
ID: 16706788
Look at the query you try to run, add a row like this before the error row:

msgbox currentdb.querydefs("qryshell").sql

(before DoCmd.OpenQuery "qryshell", acNormal, acEdit)

From there I think you will see if something's strange. Or give us the sql-string you get from the msgbox to help you.

You could also have a look at the query designer - open qryshell and then select SQL view.
0
 

Author Comment

by:attachie
ID: 16736227
sorry, everyone i have been ill the past couple days....

but the start_date and end_date is the name of the text boxes (TRAIN)
0
 

Author Comment

by:attachie
ID: 16736303
Train , i believe your solution worked fine for now , thank you
0
 
LVL 3

Expert Comment

by:trainsdse
ID: 16738668
You're welcome, the default property of a textbox is it's text property, so start_date should work the same as start_date.text  

Thomas
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Progress

850 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