Datatype error in criterion selection

[Mahendra1000]

I have code like

Option Compare Database
Option
Explicit

Private Sub
TotalReport_Click()
DoCmd.SetWarnings False

DoCmd.OutputTo acOutputQuery, "Months", "ExcelWorkbook(*.xlsx)", "D:\Files\Database\Excel\Monthstime.xlsx", False, "", , acExportQualityPrint

DoCmd.OutputTo acOutputQuery, "Quarters", "ExcelWorkbook(*.xlsx)", "D:\Files\Database\Excel\Quarterstime.xlsx", False, "", , acExportQualityPrint

MsgBox "Success!!", vbInformation, ""

DoCmd.SetWarnings True

FollowHyperlink "D:\Personal\NewFolder\PPT\TotalPresentation.p ptx"

Like the code continues..................

The function of the code is to open an PPT by linking to excel files. It woked perfect until last month. Don't know what happened few days back but when we click on the button it returning an error as ""Datatype error in criterion selection". When I debug the code I found that there is an error in the redmark code. When I went into the file path I came to know that the "Quarterstime" file is deleted.



from the backup folder I copied the "Quarterstime" file and pasted it in the specified location ."D:\Files\Database\Excel\"

When I click the button to run the code it again returning the same error as datatype error in criterion selection and error at the same line (quarterstime). When I look into the folder the file automatically got deleted (I tried it for 3 times and found that the quartersfile is going to be deleted automatically due to datatype error)

I just don't know why is this happening. What would be the reasons for it and how can i solve this issue and to move on a right track.

[ranman256]

dont bother filling in these....they are defaults...
False, "", , acExportQualityPrint

just
DoCmd.OutputTo acOutputQuery, "Quarters", acFormatXLSX , "D:\Files\Database\Excel\Quarterstime.xlsx"

[Mahendra1000]

I have around 8 excel files in my coding part. To be more convinient I just copied only 2 lines of coding, one for monthstime file and the other for the quarterstime file.

Do I need to change the coding for

DoCmd.OutputTo acOutputQuery, "Quarters", "ExcelWorkbook(*.xlsx)", "D:\Files\Database\Excel\Quarterstime.xlsx", False, "", , acExportQualityPrint

as ur given code

DoCmd.OutputTo acOutputQuery, "Quarters", acFormatXLSX , "D:\Files\Database\Excel\Quarterstime.xlsx"

Does it formats the excel file accoring to the datataype?

[ranman256]

both the constant: acFormatXLSX and the text version are the same.

[John_G]

The error message suggests the problem is not your code, but in the query "Quarters". If you open it in query design mode and run it, does it run properly?

[Mahendra1000]

The error message suggests the problem is not your code, but in the query "Quarters". If you open it in query design mode and run it, does it run properly?

Sorry for the late reply over here.

I just debugged the quarter query and it is not working fine.

I have 15 columns in main table and from that I have created a subform with only 4 columns for quarters.

When I run that subform it resulted in "data type conflict error". so, I copied the subform and in the copied form I deleted all the 4 columns and tried to insert each column and run it. It succeeded in 3columns and it ran correctly but when I inserted the 4th column with the criteria selection it returning an error. If I deleted the criteria then it's working properly. What is the error with the below criteria.

If(Month(Date())<4;"1. Quartal";If(Month(Date())<7 And Month(Date())>3;"2. Quartal";If(Month(Date())>7 And Month(Date())<10;"3. Quartal";If(Month(Date())>9 And Month(Date())<13;"4. Quartal";0))))

I can provide much information if u need.

Please help me out.

[Mahendra1000]

I have actually two subtables

one for months and the other for quarters

Months subtable is working fine and the quarters is resulting in "data type error in criteria selection"

I have the query for months (GROUP BY) as below

If(Month(Date())=1;"January";If(Month(Date())=2;"F ebruary";If(Month(Date())=3;"March";If(Month(Date( ))=4;"April";If(Month(Datum())=5;"May";If(Month(Da tum())=6;"June";If(Month(Datum())=7;"July";If(Mont h(Datum())=8;"August";If(Month(Datum())=9;"Septemb er";If(Month(Datum())=10;"October";If(Month(Datum( ))=11;"November";If(Month(Datum())=12;"December";0 ))))))))))))

It worked perfect

For the quarters (GROUP BY) query

If(Month(Date())<4;"1. Quartal";If(Month(Date())<7 And Month(Date())>3;"2. Quartal";If(Month(Date())>7 And Month(Date())<10;"3. Quartal";If(Month(Date())>9 And Month(Date())<13;"4. Quartal";0))))

It's not working for past few days.

There is an EMPTY in all the fields (Table's last row). It may be the reason for not working with Quarters query?

[John_G]

I might be missing something here, but what is the When() function? I have never seen it and I can find no reference to it. If it is a function in your database, please post the code for it.

Null fields (MS Access uses "Null", not "Empty" for table/query fields) might be causing the problem, but it depends on what When() does.

[Mahendra1000]

Thankis for the reply John

Am so sorry, it is not when functuion it is If function..

[Mahendra1000]

Let me know if u need any snippets from my code......

[Mahendra1000]

Could any one of you please enlighten me over here

[John_G]

The first thing I notice is that your Iif expression is not using a date from your data to determine a value - it is using the system date (of course, this is maybe what you want?)

If(Month(Date())<4;"1. Quartal";If(Month(Date())...... The parts in red represent the current date.

You can simplify your expression with the datepart and choose functions:

choose(datepart("q",date()),"1. Quartal","2. Quartal","3. Quartal","4. Quartal")

How and where are you using that expression? I assume it is in a query - but where?

[Mahendra1000]

The first thing I notice is that your Iif expression is not using a date from your data to determine a value - it is using the system date (of course, this is maybe what you want?)

If(Month(Date())<4;"1. Quartal";If(Month(Date())...... The parts in red represent the current date.

You can simplify your expression with the datepart and choose functions:

choose(datepart("q",date()),"1. Quartal","2. Quartal","3. Quartal","4. Quartal")

How and where are you using that expression? I assume it is in a query - but where?

Dear John, Thanks for your help and support. I debugged the error. The error is

IIf(Month(Date())<4;"1. Quartal";IIf(Month(Date())<7 And Month(Date())>3;"2. Quartal";IIf(Month(Date())>7 And Month(Date())<10;"3. Quartal";IIf(Month(Date())>9 And Month(Date())<13;"4. Quartal";0))))

Inplace of 7 there needs to be 6. I corrected it and it works fine.