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 8 3101
-----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.
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.
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.
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.
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.
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. >
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Donna Sabol |
last post by:
First, I should start by saying I am creating a database to be used by
some very impatient, non-computer literate people. It needs to be
seameless in it's operation from their point of view. I...
|
by: peggyfiorentino |
last post by:
I'm an intermediate Access user. I have 3 summary reports that I would
like to combine on one report.
I tried to create a report in design view using 3 seperate subreport
boxes. Specifying the...
|
by: Bill R via AccessMonster.com |
last post by:
I have a query:
SELECT tblCalendar.CalendarDay AS LastSunday
FROM tblCalendar
WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
|
by: PC Datasheet |
last post by:
An Access user saw my name in a newsgroup and sent me a request for help on
a project. As part of the project, a list of the dates in a month was
needed. For anyone needing a list of dates in a...
|
by: pitfour.ferguson |
last post by:
My dbase has the start date and end date of each visit. How can I ask
Access to list the day of the week of the start (easy), end (easy) and,
more importantly, the dates of the visit itself - ie...
|
by: M Skabialka |
last post by:
I am creating my first Visual Studio project, an inventory database. I have
created a form and used written directions to add data from a table to the
form using table adapters, data sets, etc.
...
|
by: newnewbie |
last post by:
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the...
|
by: GiftX |
last post by:
Hellow everyone,
I am calling/manipulating Excel spreadsheet through VB 6.0. Most of my objectives are accomplished except some cosmetics.
Problem#1:
For example, after running my program to...
|
by: pkj7461 |
last post by:
Hi,
I have a crosstab query that always displays last 8 days worth of data(Date(),Date()-1,Date()-3, etc)).
Some of these columns are blanks and MS Access, by default, returns only those columns...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |