Tech Support Guy banner
Status
Not open for further replies.

Check for Date datatype and null values in Parameter query

3K views 7 replies 4 participants last post by  Ziggy1 
#1 ·
Hi all,

I'm using a query and displaying the result in a listbox.

The query is will ask the user to enter the Start Date and End Date and then display the result in the listbox.

1) How can I set the datatype for the prompt in query? Set the Start Date & End Date to date data type?

2) If user did not enter anything, how can I tell user to enter a date before clicking OK button?

Thank you!
 
#2 ·
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
 
#3 ·
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....
 
#4 ·
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]

[edit2]
spotted a typo
[/edit2]
 
#5 ·
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.
 
#7 ·
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 :eek: Sorry guys. I'll butt out.

chris.
 
#8 ·
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.
Top