test for empty values in date formatted text boxes

[Gertjan van Heijst]

Hi,

I really hope someone can help me because I've already spend 2 days on
this problem and I'm not getting anywhere. I think the problem is that
I don't really understand how text boxes store 'empty' values. I'm
trying tot do the following.

I have a continous sub form that lists transactions. On the top level
form I have some text boxes to let the user specify the transactions
between which dates should be listed. To do this I have two text boxes
with a date format: one for the from date (ctlFrom) and ane for the to
date (ctlTo). Whenever one of the two boxes are updated I requery the
subform. When the boxes are empty I want the query to return all
transactions. The query I use for the subform is:

SELECT account, date, amount, specification FROM tblTransactions
WHERE
((date > Forms!MainForm!ctlFrom) OR (Forms!MainForm!ctlFrom=""))
AND
((date > Forms!MainForm!ctlTo) OR (Forms!MainForm!ctlTo=""));

This works when the text boxes (ctlFrom and ctlTo) contain date values
but not when the text boxes are empty. I've also tried to use NULL
instead of the empty string but the results are the same. Does anyone
know what value I should use to test for an empty box, or perhaps
there are better approaches to what I'm doing.

Thanks, Gertjan

[Deano]

Gertjan van Heijst wrote:

Hi,

I really hope someone can help me because I've already spend 2 days on
this problem and I'm not getting anywhere. I think the problem is that
I don't really understand how text boxes store 'empty' values. I'm
trying tot do the following.

I have a continous sub form that lists transactions. On the top level
form I have some text boxes to let the user specify the transactions
between which dates should be listed. To do this I have two text boxes
with a date format: one for the from date (ctlFrom) and ane for the to
date (ctlTo). Whenever one of the two boxes are updated I requery the
subform. When the boxes are empty I want the query to return all
transactions. The query I use for the subform is:

SELECT account, date, amount, specification FROM tblTransactions
WHERE
((date > Forms!MainForm!ctlFrom) OR (Forms!MainForm!ctlFrom=""))
AND
((date > Forms!MainForm!ctlTo) OR (Forms!MainForm!ctlTo=""));

This works when the text boxes (ctlFrom and ctlTo) contain date values
but not when the text boxes are empty. I've also tried to use NULL
instead of the empty string but the results are the same. Does anyone
know what value I should use to test for an empty box, or perhaps
there are better approaches to what I'm doing.

Thanks, Gertjan

Just before you requery you could test for the values of the textboxes. I
presume this is in the after_update event.

e.g
If isnull(me.ctlFrom.value) Then
'use different query that selects all records
Else
'use query you specified above
End if

hth
Martin

[dogwalker]

shouldn't it be date <= Forms!MainForm!ctlTo?

"Gertjan van Heijst" <gv******@xs4all.nl> wrote in message
news:55*************************@posting.google.co m...

Hi,

I really hope someone can help me because I've already spend 2 days on
this problem and I'm not getting anywhere. I think the problem is that
I don't really understand how text boxes store 'empty' values. I'm
trying tot do the following.

I have a continous sub form that lists transactions. On the top level
form I have some text boxes to let the user specify the transactions
between which dates should be listed. To do this I have two text boxes
with a date format: one for the from date (ctlFrom) and ane for the to
date (ctlTo). Whenever one of the two boxes are updated I requery the
subform. When the boxes are empty I want the query to return all
transactions. The query I use for the subform is:

SELECT account, date, amount, specification FROM tblTransactions
WHERE
((date > Forms!MainForm!ctlFrom) OR (Forms!MainForm!ctlFrom=""))
AND
((date > Forms!MainForm!ctlTo) OR (Forms!MainForm!ctlTo=""));

This works when the text boxes (ctlFrom and ctlTo) contain date values
but not when the text boxes are empty. I've also tried to use NULL
instead of the empty string but the results are the same. Does anyone
know what value I should use to test for an empty box, or perhaps
there are better approaches to what I'm doing.

Thanks, Gertjan

[Tammy Sanders]

Test ctlFrom and ctlTo prior to constructing your query, if they are null,
assign them values that will include all the data in your table, IE:
ctlFrom = #1/1/1900#
ctlTo = Now()

"Gertjan van Heijst" <gv******@xs4all.nl> wrote in message
news:55*************************@posting.google.co m...

Hi,

I really hope someone can help me because I've already spend 2 days on
this problem and I'm not getting anywhere. I think the problem is that
I don't really understand how text boxes store 'empty' values. I'm
trying tot do the following.

I have a continous sub form that lists transactions. On the top level
form I have some text boxes to let the user specify the transactions
between which dates should be listed. To do this I have two text boxes
with a date format: one for the from date (ctlFrom) and ane for the to
date (ctlTo). Whenever one of the two boxes are updated I requery the
subform. When the boxes are empty I want the query to return all
transactions. The query I use for the subform is:

SELECT account, date, amount, specification FROM tblTransactions
WHERE
((date > Forms!MainForm!ctlFrom) OR (Forms!MainForm!ctlFrom=""))
AND
((date > Forms!MainForm!ctlTo) OR (Forms!MainForm!ctlTo=""));

This works when the text boxes (ctlFrom and ctlTo) contain date values
but not when the text boxes are empty. I've also tried to use NULL
instead of the empty string but the results are the same. Does anyone
know what value I should use to test for an empty box, or perhaps
there are better approaches to what I'm doing.

Thanks, Gertjan

[Anachoresis]

"Gertjan van Heijst" <gv******@xs4all.nl> wrote in message
news:55*************************@posting.google.co m...

<snip>

SELECT account, date, amount, specification FROM tblTransactions
WHERE
((date > Forms!MainForm!ctlFrom) OR (Forms!MainForm!ctlFrom=""))
AND
((date > Forms!MainForm!ctlTo) OR (Forms!MainForm!ctlTo=""));

This will work:

between iif(isnull(Forms!MainForm!ctlFrom),[date],Forms!MainForm!ctlFrom)
AND
iif(isnull(Forms!MainForm!ctlTo),[date],Forms!MainForm!ctlTo)

Also, I'd advise against a field named date, since there is a function with
the same name. It could cause confusion or errors at some point. Use
dateVal or somesuch thing instead.

HTH
Yon-Paul

Thanks, Gertjan

[Douglas J. Steele]

Or possibly

If Len(Trim$(myTextBox.Value & "")) = 0 Then

to also catch those cases where the textbox contains one or more spaces, and
nothing else.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:g9********************************@4ax.com...

The *BEST* way to look for either empty or null textbox values
(regardless if it's a date, number or text) is to use the syntax:

if len(myTextBox.Value & "") = 0 then

The & "" part above supplies a length of 0 to a Null (or empty) value,
but does nothing if it has anything in it.

You can also use this when you're going through recordsets.

On 30 Aug 2003 05:16:59 -0700, gv******@xs4all.nl (Gertjan van Heijst)
wrote:

I really hope someone can help me because I've already spend 2 days on
this problem and I'm not getting anywhere. I think the problem is that
I don't really understand how text boxes store 'empty' values. I'm
trying tot do the following.

--
You Have Two Choices For Dinner: Take It Or Leave It.

[rkc]

"Douglas J. Steele" <dj******@canada.com> wrote in message
news:xK********************@news01.bloor.is.net.ca ble.rogers.com...

Or possibly

If Len(Trim$(myTextBox.Value & "")) = 0 Then

to also catch those cases where the textbox contains one or more spaces, and nothing else.

Is it possible to enter one or more spaces, and nothing else, into a textbox
via the keyboard?

[Lyle Fairfield]

"rkc" <rk*@yabba.dabba.do.rochester.rr.com> wrote in
news:nu*******************@twister.nyroc.rr.com:

Is it possible to enter one or more spaces, and nothing else, into a
textbox via the keyboard?

It's not possible here in Canada (for me, anyway), but I've always assumed it
was there in the USA to faciliate entry of "Reasons for the Invasion of
Iraq", or "Location of Iraq's WMD Caches"?

--
Lyle