not sure why you are worried about checking data types, you just need an input mask either in the table or on the form....my example uses a form with 2 date fields and a command button to run the query...
Use this criteria for your date in the query....
>=([Forms]![YourFormName]![txtFrom]) And <=([Forms]![YourFormName]![txtTo])
then this code behind the button running the query/report....it can be streamlined better but this works...
Code:
Private Sub Command52_Click()
txtFrom.SetFocus
If txtFrom.Text = "" Then
MsgBox "You must enter a Start Date"
cmdFrom.SetFocus
txtFrom.Enabled = False
Exit Sub
End If
txtTo.SetFocus
If txtTo.Text = "" Then
MsgBox "You must enter a End Date"
cmdTo.SetFocus
txtTo.Enabled = False
Exit Sub
End If
End sub
CinBar, is this essentially the same thing you asked about later in this thread? http://forums.techguy.org/business-applications/690392-validate-date-day-1-31-a.html
Please, don't cross-post or double-post. Not only is it in violation of the rules, ( http://www.techguy.org/rules.html )
but it is quite annoying to those of us here who wonder why we get no response...I had been at the other thread several times, then stumbled across this one - where Ziggy had really already answered the other thread's question....
I don't think of this as a double-post Slurpee; they're different questions and merit different threads, IMO.
For Ziggy's post, I don't know how to use the QBE editor but (using your sample) wouldn't the criteria be
BETWEEN ([Forms]![YourFormName]![txtFrom]) AND ([Forms]![YourFormName]![txtTo])
? Since the data type is implicitly Text (based on the way it's being pulled), the comparison operators you're using perform an ASCII comparison unless you convert the strings to Date/Time values, after which they'd perform double-floating-point comparisons.
Also, the best way to test whether a text box control is empty or not is to use
Code:
Len([i]textboxcontrolname[/i] & vbNullString) > 0
not a zero-length string (ZLS). I only say that because if the text box controls hadn't even been entered, I think there's a chance their value would be Null not a ZLS, so your code might throw a type mismatch error.
chris.
[edit]
Then again, this might illustrate my deficiency when it comes to bound forms and bang-bracket notation. If the form is bound, Ziggy's criteria might work because bang-bracket notation carries with it an undocumented pointer to the control's source data type. In which case the comparison operators would work because dates are calculated as doubles.
But it's important to remember that when dealing with Date data in standard SQL, the proper comparison operator for date ranges is BETWEEN...AND.
[/edit]
Agh - this is silly. Sorry. I'll post it, but it isn't worth being worried about - I just noticed the OP was a relative newbie and I wanted to give some guidance. But you point is noted - in some ways, I have to say, I don't go there, so I can't argue the tricky points of the question.
On the other hand (and there always is one!!!) Chris, while I don't really care to argue (except for silly reasons) - IMHO I think the two questions could have easily been asked during the course of the same thread. Even more, the poster has yet to return to the other post with a response. You and I hit both, but jimr and OBP didn't yet. And they were posted about 1.5 hours apart - and no one had responded to this first one yet (and wouldn't for 19 hours - although the later one I would get to in about 12 hours). A bump or an posting by the OP of the other question here would have kept all the answers coming to one thread.
chris, the example I posted is a working method I use, there's nothing wrong with posting an alternative method but you should test the ones suggested as well, and not undermine my reponse.
Sorry, I didn't mean to undermine your suggestion at all. I assumed your solution was tested when I made my post, but I think what I said still has some validity. I was merely asking if it wouldn't be more appropriate to use BETWEEN...AND because it seems to me to be the more logically semantic operator for a Date/Time data type query. But like I said, I don't even know if QBE supports BETWEEN, or if it does, how it's used.
Oh...I see how I worded my edit poorly. When I said "might," I meant "this might be why it works" not "this may or may not work." Sorry about that.
It's just that there's an implicit type conversion somewhere in the WHERE clause you've provided, and I'm trying to make sense of how QBE gets from point A to point B.
But either way, I didn't mean to suggest your criteria clause was erroneous. I'm really sorry if you took it that way.
And I didn't mean to nitpick about the ZLS issue; I was trying to offer some constructive criticism. I really do believe what you posted could throw a type mismatch under certain conditions.
Boy, and slurpee's after me too Sorry guys. I'll butt out.
Chris..I'm over it It was the way you came across, and the fact that you did not know if mine would work or not.
Your example works identical to mine, I am not aware of any issues. I don't dig to deep into the technicalities...
SELECT Table1.date1
FROM Table1
WHERE (((Table1.date1)>=([Forms]![form1]![txtFrom]) And (Table1.date1)<=([Forms]![form1]![txtTo])));
SELECT Table1.date1
FROM Table1
WHERE (((Table1.date1) Between ([Forms]![form1]![txtFrom]) And ([Forms]![form1]![txtTo])));
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
860K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!