number of columns doesn't match bug in Access

[Grd]

Hi,

I have a union query which regularily generates the "number of columns
doesn't match" problem in Access. This happens when I run the query through a
VBA code moduel - specifically I'm doing a DSum that referes to a query that
is based upon this Union query. So when this DSUM does its thing sometimes I
get this problem.
Also it is inconsistent so sometimes it comes up (about half the time)
sometimes it doesn't. I use the VBA code On Error resume to get it to work -
but its slowing down my application.

When I run it directly by double clicking on it in the Database window there
are no problems. (The number of columns ofcourse do match)

I put the ALL word in the UNION statement to help with the problem but to no
avail.

Is there an alternative to DSUM or something that will not trigger this bug??

Thanks in advance
Gordon

 

[Jerry Whittle]

Please post the SQL so that we can look at what's going all.

UNION ALL should have nothing to do with columns. ALL returns all records
whereas omitting the word ALL causes the database to not return duplicates.

 

[Grd]

Hi Jerry,

Thanks for the reply.

My columns do match, as they must in a Union Join. The error is not with my
SQL but with Access. The SQL is reasonably complex and this might be causing
Access to fall over and generate the error (thats why I put Union All instead
of Union to help with the execution). I have read about this flaw in Access
in other postings and was wondering if there were ways to avoid it.

I'll post the SQL. As you can see it involves a few if statements,
parameters from Forms and other bits. It works perfectly when run. It works
perfectly when run from another query. However it doesn't behave itself
always when run from a DSUM function in code and this causes the intermittent
error. So inconsitent is this error that it will happen one out of five times
and I've put an On Error Resume to deal with it. It fixes the problem usually
but it can slow down the execution of the code.


Heres the SQL:

SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC Op","PC",[Shop])
AS ShopCorrection, ISO14001_Internal_Audit_Results.Open As OpenClosedNumbers,
"Open" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod]

UNION ALL SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC
Op","PC",[Shop]) AS ShopCorrection, ISO14001_Internal_Audit_Results.Closed,
"Closed" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod];

Thanks in advance for any help or advice you may have
Gordon

 

[Jerry Whittle]

I'm guessing that the problem revolves around the [Date] field.

1. Date is a reserved word in Access as Date() is a function. Looks like you
have brackets around the Date which should stop any such problems but who
knows. Naming the field something like ReportDate would fix any potential
problems. Also you use [Month] as an alias in the SQL. It's also a reserved
word.

2. The Date field isn't a Date datatype which has you jumping through
hoops. Evidently people can put in stuff like "FY" in the Date field. The
potential for problems goes way up.

3. Speaking of which you are using the CDate function to convert the text to
an actual date. On problem with CDate is that it will fail miserably if it
runs into something that it can convert into a date. I always, and I mean
always, use the IsDate function first to evaluate if the text can possibly be
evaluated as a date before passing it to CDate. Something like below:

SELECT tblDatesText.[date]
FROM tblDatesText
WHERE (((IsDate([date]))=True));
Or
SELECT IIf(IsDate([date])=True,[date],#1/1/1950#) AS NotDate
FROM tblDatesText;

I suggest looking for any of the Date field data that doesn't look like
"FY*" and return a False (0 is False and -1 is True) for the IsDate. You
might just have so dirty data causing intermittent problems.

If you ever get the chance, redesign the database to make that Date field an
actual Date/Time datatype. You'll be glad that you did.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry,

Thanks for the reply.

My columns do match, as they must in a Union Join. The error is not with my
SQL but with Access. The SQL is reasonably complex and this might be causing
Access to fall over and generate the error (thats why I put Union All instead
of Union to help with the execution). I have read about this flaw in Access
in other postings and was wondering if there were ways to avoid it.

I'll post the SQL. As you can see it involves a few if statements,
parameters from Forms and other bits. It works perfectly when run. It works
perfectly when run from another query. However it doesn't behave itself
always when run from a DSUM function in code and this causes the intermittent
error. So inconsitent is this error that it will happen one out of five times
and I've put an On Error Resume to deal with it. It fixes the problem usually
but it can slow down the execution of the code.


Heres the SQL:

SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC Op","PC",[Shop])
AS ShopCorrection, ISO14001_Internal_Audit_Results.Open As OpenClosedNumbers,
"Open" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod]

UNION ALL SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC
Op","PC",[Shop]) AS ShopCorrection, ISO14001_Internal_Audit_Results.Closed,
"Closed" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod];

Thanks in advance for any help or advice you may have
Gordon

Please post the SQL so that we can look at what's going all.

UNION ALL should have nothing to do with columns. ALL returns all records
whereas omitting the word ALL causes the database to not return duplicates.

 

[Grd]

Hi Jerry,

Thanks very much for looking at my SQL.

The naming of the fields using reserved words could be an source of problems
so I'll look into what can be done there.

Interesting what you say about the CDate function and using IsDate - that is
a nice precaution. Worth trying.

I'll post back if I get somewhere with this problem

Thanks again Jerry,

Gordon

I'm guessing that the problem revolves around the [Date] field.

1. Date is a reserved word in Access as Date() is a function. Looks like you
have brackets around the Date which should stop any such problems but who
knows. Naming the field something like ReportDate would fix any potential
problems. Also you use [Month] as an alias in the SQL. It's also a reserved
word.

2. The Date field isn't a Date datatype which has you jumping through
hoops. Evidently people can put in stuff like "FY" in the Date field. The
potential for problems goes way up.

3. Speaking of which you are using the CDate function to convert the text to
an actual date. On problem with CDate is that it will fail miserably if it
runs into something that it can convert into a date. I always, and I mean
always, use the IsDate function first to evaluate if the text can possibly be
evaluated as a date before passing it to CDate. Something like below:

SELECT tblDatesText.[date]
FROM tblDatesText
WHERE (((IsDate([date]))=True));
Or
SELECT IIf(IsDate([date])=True,[date],#1/1/1950#) AS NotDate
FROM tblDatesText;

I suggest looking for any of the Date field data that doesn't look like
"FY*" and return a False (0 is False and -1 is True) for the IsDate. You
might just have so dirty data causing intermittent problems.

If you ever get the chance, redesign the database to make that Date field an
actual Date/Time datatype. You'll be glad that you did.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hi Jerry,

Thanks for the reply.

My columns do match, as they must in a Union Join. The error is not with my
SQL but with Access. The SQL is reasonably complex and this might be causing
Access to fall over and generate the error (thats why I put Union All instead
of Union to help with the execution). I have read about this flaw in Access
in other postings and was wondering if there were ways to avoid it.

I'll post the SQL. As you can see it involves a few if statements,
parameters from Forms and other bits. It works perfectly when run. It works
perfectly when run from another query. However it doesn't behave itself
always when run from a DSUM function in code and this causes the intermittent
error. So inconsitent is this error that it will happen one out of five times
and I've put an On Error Resume to deal with it. It fixes the problem usually
but it can slow down the execution of the code.


Heres the SQL:

SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC Op","PC",[Shop])
AS ShopCorrection, ISO14001_Internal_Audit_Results.Open As OpenClosedNumbers,
"Open" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod]

UNION ALL SELECT Format(([Date]),"mmm yy") AS [Month], IIf([shop]="PC
Op","PC",[Shop]) AS ShopCorrection, ISO14001_Internal_Audit_Results.Closed,
"Closed" AS Type, CDate([date]) AS DateConvertedToDate
FROM ISO14001_Internal_Audit_Results
WHERE (((Format(([Date]),"mmm yy")) Not Like "fy*") AND ((IIf([shop]="PC
Op","PC",[Shop]))=IIf([Forms]![frmChooseReport]![lstShops]="","Company",[Forms]![frmChooseReport]![lstShops])))
AND CDate([Date]) Between
[Forms]![frmChooseReport]![txtBeginningReportPeriod] And
[Forms]![frmChooseReport]![txtEndReportPeriod];

Thanks in advance for any help or advice you may have
Gordon

Please post the SQL so that we can look at what's going all.

UNION ALL should have nothing to do with columns. ALL returns all records
whereas omitting the word ALL causes the database to not return duplicates.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

Hi,

I have a union query which regularily generates the "number of columns
doesn't match" problem in Access. This happens when I run the query through a
VBA code moduel - specifically I'm doing a DSum that referes to a query that
is based upon this Union query. So when this DSUM does its thing sometimes I
get this problem.
Also it is inconsistent so sometimes it comes up (about half the time)
sometimes it doesn't. I use the VBA code On Error resume to get it to work -
but its slowing down my application.

When I run it directly by double clicking on it in the Database window there
are no problems. (The number of columns ofcourse do match)

I put the ALL word in the UNION statement to help with the problem but to no
avail.

Is there an alternative to DSUM or something that will not trigger this bug??

Thanks in advance
Gordon