Datatype mismatch

R

redtux

I have the following code which works without anty problem

Dim dir_save As String
Dim date1 As String
date1 = format(Now(), "yyyymmddhm")


dir_save = "h:\"
DoCmd.TransferText acExportDelim, , "dump_all", dir_save & "feedback" &
date1 & ".csv", True
DoCmd.TransferText acExportDelim, , "tb_progress", dir_save &
"progress.csv", True
DoCmd.TransferText acExportDelim, , "tb_client", dir_save & "client.csv", True
DoCmd.TransferText acExportDelim, , "lk_org", dir_save & "lk_org.csv", True
DoCmd.TransferText acExportDelim, , "lk_stages", dir_save & "lk_stages.csv",
True

However

If I run this

Dim Report As String
Dim format As Variant
Dim filename As String
Dim dir As String
Dim date4 As String
Dim subdir As String
Dim dir_save As String
'Dim officer As String
'Stage = DLookup("[stage]", "lk_stages", "[stage]= Stage_reached")
Dim fs
date4 = format("11/13/2006", "yyyymmdd")
Set fs = CreateObject("scripting.filesystemobject")
dir = CurrentDBDir()


subdir = dir & "outstanding" & officer

If Not fs.folderexists(subdir) Then MkDir subdir
dir_save = subdir & "\" & date4 & "\"
'dir_save = subdir & "\"



Report = "rp_outstanding_by_officer"

format = "SNP"
filename = dir_save & "\" & officer & "." & format

DoCmd.OutputTo acOutputReport, Report, acFormatSNP, filename, False

I get data type mismatch on the date format line

Can anyone explain why the same code works in one module and not another
(Same Database access XP)
 
S

Stefan Hoffmann

hi,
date4 = format("11/13/2006", "yyyymmdd")
I get data type mismatch on the date format line
Can anyone explain why the same code works in one module and not another
(Same Database access XP)
Click to expand...
Sure?

Both work

format("11/13/2006", "yyyymmdd")
format(#11/13/2006#, "yyyymmdd")

without error under de-DE and en-US.


mfG
--> stefan <--
 
D

Douglas J. Steele

Assuming that by "date format line" you mean date4 = format("11/13/2006",
"yyyymmdd"), dates are supposed to be delimited with #, not quotes:

date4 = Format(#11/13/2006#, "yyyymmdd")

or

date4 = Format(DateSerial(2006, 11, 13), "yyyymmdd")

Having said that, though, Format("11/13/2006", "yyyymmdd") worked fine for
me.
 
R

redtux

Douglas J. Steele said:
Assuming that by "date format line" you mean date4 = format("11/13/2006",
"yyyymmdd"), dates are supposed to be delimited with #, not quotes:

date4 = Format(#11/13/2006#, "yyyymmdd")

or
Click to expand...

sorr this was something I was testing - the origiginal line is
date4=Format(now(), "yyyymmdd")

which works in one module, but not in another
date4 = Format(DateSerial(2006, 11, 13), "yyyymmdd")

Having said that, though, Format("11/13/2006", "yyyymmdd") worked fine for
me.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


redtux said:
I have the following code which works without anty problem

Dim dir_save As String
Dim date1 As String
date1 = format(Now(), "yyyymmddhm")


dir_save = "h:\"
DoCmd.TransferText acExportDelim, , "dump_all", dir_save & "feedback" &
date1 & ".csv", True
DoCmd.TransferText acExportDelim, , "tb_progress", dir_save &
"progress.csv", True
DoCmd.TransferText acExportDelim, , "tb_client", dir_save & "client.csv",
True
DoCmd.TransferText acExportDelim, , "lk_org", dir_save & "lk_org.csv",
True
DoCmd.TransferText acExportDelim, , "lk_stages", dir_save &
"lk_stages.csv",
True

However

If I run this

Dim Report As String
Dim format As Variant
Dim filename As String
Dim dir As String
Dim date4 As String
Dim subdir As String
Dim dir_save As String
'Dim officer As String
'Stage = DLookup("[stage]", "lk_stages", "[stage]= Stage_reached")
Dim fs
date4 = format("11/13/2006", "yyyymmdd")
Set fs = CreateObject("scripting.filesystemobject")
dir = CurrentDBDir()


subdir = dir & "outstanding" & officer

If Not fs.folderexists(subdir) Then MkDir subdir
dir_save = subdir & "\" & date4 & "\"
'dir_save = subdir & "\"



Report = "rp_outstanding_by_officer"

format = "SNP"
filename = dir_save & "\" & officer & "." & format

DoCmd.OutputTo acOutputReport, Report, acFormatSNP, filename, False

I get data type mismatch on the date format line

Can anyone explain why the same code works in one module and not another
(Same Database access XP)
Click to expand...
Click to expand...
 
S

Stefan Hoffmann

hi,
sorr this was something I was testing - the origiginal line is
date4=Format(now(), "yyyymmdd")
which works in one module, but not in another
Click to expand...
Check wether your variable date4 is really declared as String, otherwise
i would explain your type mismatch error.

Place an Option Explicit in the first line of your module.

mfG
--> stefan <--
 
R

redtux

Stefan Hoffmann said:
hi,

Check wether your variable date4 is really declared as String, otherwise
i would explain your type mismatch error.
Click to expand...
yep at the top of function
Place an Option Explicit in the first line of your module.
Click to expand...

It is
mfG
--> stefan <--
Click to expand...

I have even copied the the function that worked into this module and that
still works
Module is compiled as well - no errors
 
S

Stefan Hoffmann

hi,

found it finally...
Dim fs
date4 = format("11/13/2006", "yyyymmdd")
Click to expand...
It's not the date causing the error.
Set fs = CreateObject("scripting.filesystemobject")
Click to expand...
It's this line. fs is declared as Variant, cause you omitted the data type.

Dim fs As Object

will work.


mfG
--> stefan <--
 
R

redtux

Stefan Hoffmann said:
hi,

found it finally...

It's not the date causing the error.

It's this line. fs is declared as Variant, cause you omitted the data type.

Dim fs As Object

will work.
Click to expand...

no difference I'm afraid
 
S

Stefan Hoffmann

hi,
no difference I'm afraid
Click to expand...
Now i've got you:

Dim format As Variant

Nice declaration. It has precedence over VBA.Strings.Format...


mfG
--> stefan <--
 
D

davjoh123

You may want to declare your date variables as date rather than string.
You are trying to pass a date value to your variable date1 which is
declared as string.

DJ
 
S

Stefan Hoffmann

hi,

You may want to declare your date variables as date rather than string.
Click to expand...
The OP may want a string.
You are trying to pass a date value to your variable date1 which is
declared as string.
Click to expand...
Haven't read the OPs source:

--
Dim Report As String
Dim format As Variant
Dim filename As String
Dim dir As String
Dim date4 As String
Dim subdir As String
Dim dir_save As String
'Dim officer As String
'Stage = DLookup("[stage]", "lk_stages", "[stage]= Stage_reached")
Dim fs
date4 = format("11/13/2006", "yyyymmdd")
--

Here the format function is not invoked, cause his declaraction of
format as variant has precedence over VBA.Strings.Format.



mfG
--> stefan <--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word macro that can call cells out of Excel for a find and replace 0
VBA - exporting serial letters to single documents 0
Transfer Text 2
Export to File with Current Date 2
Save a .csv file to me.com 1
Refer to Variable in strSQL 2
Update fields in a contious form 0
Count # of Errors Occuring in a function 0

Top