By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Blank Dates in Query

P: n/a
I have a query, which gathers up information, which is subsequently dumped
into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple
little function with three arguments, a switch (integer) and 2 date fields.
For certain values of the switch, I use one date field, for others, the
other date field, and for switch values which are neither, I need to return
a blank.

Well, I can't put a blank string or a null in a date type variable, so I
made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that
the entries behaved (in further formulas) as if they were strings, not
dates.

Any ideas?
- Turtle
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure you can put a NULL in a DateTime column. Its probably that dang
Excel.

If the NULL is in the first row exported to Excel, Excel may "think" it
is an empty string & format the values in that column as Text data type.

Idea: You might use a Default date in place of the NULL - something
like Dec 31, 1899; then, in further formulas, but a conditional that
ignores the default date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
22JzikQPugJU+Iciq8iCLSFX
=z7Nu
-----END PGP SIGNATURE-----
MacDermott wrote:
I have a query, which gathers up information, which is subsequently dumped
into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple
little function with three arguments, a switch (integer) and 2 date fields.
For certain values of the switch, I use one date field, for others, the
other date field, and for switch values which are neither, I need to return
a blank.

Well, I can't put a blank string or a null in a date type variable, so I
made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that
the entries behaved (in further formulas) as if they were strings, not
dates.


Nov 13 '05 #2

P: n/a
Yes, I can put a null in a date/time column, but I get a type mismatch if I
try to put one in a date-type variable.

This code throws an error on the line marked ******

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Date
Dim TRD as Date
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null ********
End Select
TheRightDate=TRD
End Function

"MGFoster" <me@privacy.com> wrote in message
news:H9****************@newsread3.news.pas.earthli nk.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure you can put a NULL in a DateTime column. Its probably that dang
Excel.

If the NULL is in the first row exported to Excel, Excel may "think" it
is an empty string & format the values in that column as Text data type.

Idea: You might use a Default date in place of the NULL - something
like Dec 31, 1899; then, in further formulas, but a conditional that
ignores the default date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
22JzikQPugJU+Iciq8iCLSFX
=z7Nu
-----END PGP SIGNATURE-----
MacDermott wrote:
I have a query, which gathers up information, which is subsequently dumped into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple little function with three arguments, a switch (integer) and 2 date fields. For certain values of the switch, I use one date field, for others, the
other date field, and for switch values which are neither, I need to return a blank.

Well, I can't put a blank string or a null in a date type variable, so I
made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that the entries behaved (in further formulas) as if they were strings, not
dates.

Nov 13 '05 #3

P: n/a
While you can make a Date field in a table Null, you cannot make a Date
variable Null. The only variable type than can hold a Null value is a
Variant.

Try:

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Variant
Dim TRD as Variant
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null
End Select
TheRightDate=TRD
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MacDermott" <ma********@nospam.com> wrote in message
news:Qa*****************@newsread2.news.atl.earthl ink.net...
Yes, I can put a null in a date/time column, but I get a type mismatch if I try to put one in a date-type variable.

This code throws an error on the line marked ******

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Date
Dim TRD as Date
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null ********
End Select
TheRightDate=TRD
End Function

"MGFoster" <me@privacy.com> wrote in message
news:H9****************@newsread3.news.pas.earthli nk.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure you can put a NULL in a DateTime column. Its probably that dang
Excel.

If the NULL is in the first row exported to Excel, Excel may "think" it
is an empty string & format the values in that column as Text data type.

Idea: You might use a Default date in place of the NULL - something
like Dec 31, 1899; then, in further formulas, but a conditional that
ignores the default date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
22JzikQPugJU+Iciq8iCLSFX
=z7Nu
-----END PGP SIGNATURE-----
MacDermott wrote:
I have a query, which gathers up information, which is subsequently dumped into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple little function with three arguments, a switch (integer) and 2 date fields. For certain values of the switch, I use one date field, for others, the other date field, and for switch values which are neither, I need to return a blank.

Well, I can't put a blank string or a null in a date type variable, so I made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that the entries behaved (in further formulas) as if they were strings, not
dates.



Nov 13 '05 #4

P: n/a
While you can make a Date field in a table Null, you cannot make a Date
variable Null. The only variable type than can hold a Null value is a
Variant.

Try:

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Variant
Dim TRD as Variant
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null
End Select
TheRightDate=TRD
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MacDermott" <ma********@nospam.com> wrote in message
news:Qa*****************@newsread2.news.atl.earthl ink.net...
Yes, I can put a null in a date/time column, but I get a type mismatch if I try to put one in a date-type variable.

This code throws an error on the line marked ******

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Date
Dim TRD as Date
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null ********
End Select
TheRightDate=TRD
End Function

"MGFoster" <me@privacy.com> wrote in message
news:H9****************@newsread3.news.pas.earthli nk.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure you can put a NULL in a DateTime column. Its probably that dang
Excel.

If the NULL is in the first row exported to Excel, Excel may "think" it
is an empty string & format the values in that column as Text data type.

Idea: You might use a Default date in place of the NULL - something
like Dec 31, 1899; then, in further formulas, but a conditional that
ignores the default date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
22JzikQPugJU+Iciq8iCLSFX
=z7Nu
-----END PGP SIGNATURE-----
MacDermott wrote:
I have a query, which gathers up information, which is subsequently dumped into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple little function with three arguments, a switch (integer) and 2 date fields. For certain values of the switch, I use one date field, for others, the other date field, and for switch values which are neither, I need to return a blank.

Well, I can't put a blank string or a null in a date type variable, so I made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that the entries behaved (in further formulas) as if they were strings, not
dates.


Nov 13 '05 #5

P: n/a
Thanks for the idea, Doug!

Actually, that had been my original solution.
Unfortunately, that's what dumped incorrectly into Excel.

The "work-around" I found was to declare the function to return a date, and
set the value to 0 instead of null.
Then in the query I used

MyField:IIF(TheRightDate(ADate,BDate,C)=0,Null,The RightDate(ADate,BDate,C))
This did the job, but it seemed an unnecessarily clunky way to do it (lot of
redundant calculations, for one thing).

So I was hoping somebody might have a better idea.

- Turtle

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:4I********************@rogers.com...
While you can make a Date field in a table Null, you cannot make a Date
variable Null. The only variable type than can hold a Null value is a
Variant.

Try:

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Variant Dim TRD as Variant
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null
End Select
TheRightDate=TRD
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MacDermott" <ma********@nospam.com> wrote in message
news:Qa*****************@newsread2.news.atl.earthl ink.net...
Yes, I can put a null in a date/time column, but I get a type mismatch if
I
try to put one in a date-type variable.

This code throws an error on the line marked ******

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Date
Dim TRD as Date
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null ********
End Select
TheRightDate=TRD
End Function

"MGFoster" <me@privacy.com> wrote in message
news:H9****************@newsread3.news.pas.earthli nk.net...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure you can put a NULL in a DateTime column. Its probably that dang
Excel.

If the NULL is in the first row exported to Excel, Excel may "think" it is an empty string & format the values in that column as Text data type.
Idea: You might use a Default date in place of the NULL - something
like Dec 31, 1899; then, in further formulas, but a conditional that
ignores the default date.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
22JzikQPugJU+Iciq8iCLSFX
=z7Nu
-----END PGP SIGNATURE-----
MacDermott wrote:

> I have a query, which gathers up information, which is subsequently
dumped
> into an instance of Excel using recordsetcopy.
>
> For one of the query fields, I have written what should be a pretty simple
> little function with three arguments, a switch (integer) and 2 date

fields.
> For certain values of the switch, I use one date field, for others, the > other date field, and for switch values which are neither, I need to

return
> a blank.
>
> Well, I can't put a blank string or a null in a date type variable,
so I > made my function return a variant.
> That looked great in Access, but when I dumped it into Excel, I

found that
> the entries behaved (in further formulas) as if they were strings,

not > dates.



Nov 13 '05 #6

P: n/a
Why the VB-function ?

MyField: iif((c=1) or (c=3) or (c=5),A,(iif((c=2) or (c=4) or
(c=6),B,null)))

Bas Hartkamp.

"MacDermott" <ma********@nospam.com> wrote in message
news:9P******************@newsread3.news.atl.earth link.net...
Thanks for the idea, Doug!

Actually, that had been my original solution.
Unfortunately, that's what dumped incorrectly into Excel.

The "work-around" I found was to declare the function to return a date, and set the value to 0 instead of null.
Then in the query I used

MyField:IIF(TheRightDate(ADate,BDate,C)=0,Null,The RightDate(ADate,BDate,C)) This did the job, but it seemed an unnecessarily clunky way to do it (lot of redundant calculations, for one thing).

So I was hoping somebody might have a better idea.

- Turtle

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:4I********************@rogers.com...
While you can make a Date field in a table Null, you cannot make a Date
variable Null. The only variable type than can hold a Null value is a
Variant.

Try:

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Variant
Dim TRD as Variant
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null
End Select
TheRightDate=TRD
End Function
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MacDermott" <ma********@nospam.com> wrote in message
news:Qa*****************@newsread2.news.atl.earthl ink.net...
Yes, I can put a null in a date/time column, but I get a type mismatch if
I
try to put one in a date-type variable.

This code throws an error on the line marked ******

Public Function TheRightDate(A as Date, B as Date, C as Integer) as Date Dim TRD as Date
Select case C
case 1,3,5
TRD=A
case 2,4,6
TRD=B
case else
TRD=Null ********
End Select
TheRightDate=TRD
End Function

"MGFoster" <me@privacy.com> wrote in message
news:H9****************@newsread3.news.pas.earthli nk.net...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Sure you can put a NULL in a DateTime column. Its probably that dang > Excel.
>
> If the NULL is in the first row exported to Excel, Excel may "think" it > is an empty string & format the values in that column as Text data type. >
> Idea: You might use a Default date in place of the NULL - something
> like Dec 31, 1899; then, in further formulas, but a conditional that
> ignores the default date.
>
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
>
> iQA/AwUBQX6e8YechKqOuFEgEQLZqwCePYtuYFxgHzwEdgKrQNpMKh OD1ocAoP82
> 22JzikQPugJU+Iciq8iCLSFX
> =z7Nu
> -----END PGP SIGNATURE-----
>
>
> MacDermott wrote:
>
> > I have a query, which gathers up information, which is subsequently dumped
> > into an instance of Excel using recordsetcopy.
> >
> > For one of the query fields, I have written what should be a pretty simple
> > little function with three arguments, a switch (integer) and 2 date fields.
> > For certain values of the switch, I use one date field, for others, the
> > other date field, and for switch values which are neither, I need
to return
> > a blank.
> >
> > Well, I can't put a blank string or a null in a date type

variable, so
I
> > made my function return a variant.
> > That looked great in Access, but when I dumped it into Excel, I

found that
> > the entries behaved (in further formulas) as if they were strings, not > > dates.
>



Nov 13 '05 #7

P: n/a
Try leaving the function to return a variant, then using CVDate in
your query.

CVDate(DateFunction(Switch, ADate,BDate))

CDate goes boom just as much as having a function which returns a date
value. Using CVDate (per help left around for "backwards
compatability"), will allow a null value, but still return it as a
date type.

My team relies on this like oxygen in our reports. Translating
integer date values (yyyymmdd) which are 8 digit for values, but 0 for
blanks. The zeroes are translated to nulls in our FDate function.
CVDate(FDate(DateField))

Hope this helps,
Anthony.

"MacDermott" <ma********@nospam.com> wrote in message news:<9P******************@newsread3.news.atl.eart hlink.net>...
Thanks for the idea, Doug!

Actually, that had been my original solution.
Unfortunately, that's what dumped incorrectly into Excel.

The "work-around" I found was to declare the function to return a date, and
set the value to 0 instead of null.
Then in the query I used

MyField:IIF(TheRightDate(ADate,BDate,C)=0,Null,The RightDate(ADate,BDate,C))
This did the job, but it seemed an unnecessarily clunky way to do it (lot of
redundant calculations, for one thing).

So I was hoping somebody might have a better idea.

- Turtle

Nov 13 '05 #8

P: n/a
MacDermott,
This may not help, but I've used Nz(MyDate,#1/1/1905#) in queries before so
that instead of blanks I get the date 1/1/1905. This way I can always find
the blanks by searching for my substituted date. So:
"IIF(IntegerSwitch=This,
Date1,IIF(IntegerSwitch=That,Nz(Date2,#1/1/1905#)))" would work. If you
really need a blank in those cells, then fire up an instance of Excel and
have it search and replace the #1/1/1905# with blanks.

"MacDermott" <ma********@nospam.com> wrote in message
news:_2*****************@newsread3.news.atl.earthl ink.net...
I have a query, which gathers up information, which is subsequently
dumped
into an instance of Excel using recordsetcopy.

For one of the query fields, I have written what should be a pretty simple
little function with three arguments, a switch (integer) and 2 date
fields.
For certain values of the switch, I use one date field, for others, the
other date field, and for switch values which are neither, I need to
return
a blank.

Well, I can't put a blank string or a null in a date type variable, so I
made my function return a variant.
That looked great in Access, but when I dumped it into Excel, I found that
the entries behaved (in further formulas) as if they were strings, not
dates.

Any ideas?
- Turtle

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.