Seeking Import/Link/Query workaround #Num! and #Error responses

C

catkin63

Good Morning,
I hope someone can help me before I drop-kick Office 2007 and make my IT
department put me back on 2003. **Note 2003 gave me no problems at all with
this. EVER!**
The section between ****'s is my sample data. Source is .txt file. Data is
fixed width. Import cannot recognize a negative number represented by the "-"
after a number value. Dates have been linked as text, as I cannot command the
system to ignore the #Num! error. The section between ++++'s is a sample of
the resulting link. The third section is my SQL, which works around the date
problem and the subsequent #Error problem. When I get down to the information
I need, there are still lines that I do not need, this line contains the word
description. There is now no date in the EffDt field, but when I try to
filter out records with NULL value, I get this error: Data type mismatch in
criteria expression. I am trying to append to or create another table.

QUESTION: How do I simplify this unwieldy query and/or remove the records
programatically? **Please do not respond with "correct the data first", as
with hundreds or thousands of lines, that is not time effective.**
****
26212 0003 C 1957 APT AIR CONDITIONIN 101.00 08-28-08 09-01-08
09-13-2008
26212 0004 C 1957 APT AIR CONDITIONIN 101.00 08-28-08 09-01-08
09-13-2008
26211 0002 C 1957 APT RENT 573.00 09-01-08 09-01-08
280.00
11005 0001 C ISS SPECIAL SERVICES FEE 10.00 09-11-08
0.00
11003 0001 C INTERNATIONAL STUDENT FE 50.00 09-12-08
0.00
26212 0005 C 1957 APT AIR CONDITIONIN 101.00- 09-12-08
09-13-2008
90000 0001 P PAYMENT ON ACCOUNT 400.00- 09-12-08
09-13-2008
90000 0002 P PAYMENT ON ACCOUNT 8,110.95- 09-12-08
09-13-2008
Page 8 of 9 Pages in Unit 1 Total Units 1 Next Page __ Next Unit
__
W-Z0137 - SECURITY - YOU MAY NOT UPDATE ON THIS SCREEN
Student Account Date: 09-13-2008
SubCd Seq CP Description Amount Eff Dt Bill Dt Pd
Appl
****** Future Items Begin Here *****
26211 0003 C 1957 APT RENT 573.00 10-01-08
0.00
26211 0004 C 1957 APT RENT 573.00 11-01-08
0.00
26211 0005 C 1957 APT RENT 573.00 12-01-08
0.00

End Balance: 2,072.00
****
++++
CP Description Amount Sign EffDt BillDt PdAppl
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT RENT $573.00 09-01-08 09-01-08 280.00
C ISS SPECIAL SERVICES FEE $10.00 09-11-08 0.00
C INTERNATIONAL STUDENT FE $50.00 09-12-08 0.00
C 1957 APT AIR CONDITIONIN $101.00 - 09-12-08 09-13-2008
P PAYMENT ON ACCOUNT $400.00 - 09-12-08 09-13-2008
P PAYMENT ON ACCOUNT $8,110.95 - 09-12-08 09-13-2008
Pages in Unit 1 To Next Pa e __ Ne t Unit __
E URITY - YOU MAY NOT UPDA C EEN

Student Account ate: 09- 3-2008
C Description Eff Dt Bill Dt Pd Appl
r Items Begin Here *****
C 1957 APT RENT $573.00 10-01-08 0.00
C 1957 APT RENT $573.00 11-01-08 0.00
C 1957 APT RENT $573.00 12-01-08 0.00

End Balance: $2,072.00

++++

SELECT MainframeLink.Description, MainframeLink.CP,
IIf([MainframeLink]![Sign]="-",-[MainframeLink]![Amount],[MainframeLink]![Amount])
AS Amount,
IIf(IsNull([MainframeLink]![EffDt]),"",IIf(Asc([MainframeLink]![EffDt])="32","",IIf(IsDate(DateSerial(Right([MainframeLink]![EffDt],2),Left([MainframeLink]![EffDt],2),Mid([MainframeLink]![EffDt],4,2))),DateSerial(Right([MainframeLink]![EffDt],2),Left([MainframeLink]![EffDt],2),Mid([MainframeLink]![EffDt],4,2)),"")))
AS EffDt,
IIf(IsNull([MainframeLink]![BillDt]),"",IIf(IsDate(DateSerial(Right([MainframeLink]![BillDt],2),Left([MainframeLink]![BillDt],2),Mid([MainframeLink]![BillDt],4,2))),DateSerial(Right([MainframeLink]![BillDt],2),Left([MainframeLink]![BillDt],2),Mid([MainframeLink]![BillDt],4,2)),""))
AS BillDt,
IIf(IsNull([MainframeLink]![PdAppl]),"",IIf(Asc([MainframeLink]![PdAppl])="32","",IIf(IsDate(DateSerial(Right([MainframeLink]![PdAppl],2),Left([MainframeLink]![PdAppl],2),Mid([MainframeLink]![PdAppl],4,2))),DateSerial(Right([MainframeLink]![PdAppl],2),Left([MainframeLink]![PdAppl],2),Mid([MainframeLink]![PdAppl],4,2)),""))) AS PdAppl INTO Mainframe
FROM MainframeLink
WHERE (((MainframeLink.Description)<>"BALANCE FORWARD") AND
((MainframeLink.CP)="C" Or (MainframeLink.CP)="P"));
 
J

John Spencer

First ASC function returns a number 32 not a string "32"
Second try using . as the separator instead of ! between table and field names.
Third try the following to return a date field. It is shorter and will return
a date field not a text field. Whenever you use a string ("") as one of the
options to be returned, the field will be defined as a text field. Using Null
as an option allows you to return a date.

IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@")))

The formula above does rely on the date being in mmddyy form - which is what I
deduced from your code.

SELECT MF.Description
, MF.CP
, IIf([MF].[Sign]="-",-[MF].[Amount],[MF].[Amount]) AS Amount

, IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@"))) AS EffDte,


IIF(IsDate(Format(BillDt,"@@\/@@\/@@"))= False or Asc(BillDt)= 32, Null,
CDate(Format(BillDt,"@@\/@@\/@@"))) AS BillDte,

, IIF(IsDate(Format(pdAppl,"@@\/@@\/@@"))= False or Asc(pdAppl)= 32, Null,
CDate(Format(pdAppl,"@@\/@@\/@@"))) AS PdApl
INTO Mainframe
FROM MainframeLink AS MF
WHERE (((MF.Description)<>"BALANCE FORWARD") AND
((MF.CP)="C" Or (MF.CP)="P"));

Note that I slightly changed your expression aliases. If you wish to use the
same names you were using. you will have to fully qualify the field references
in the expressions. I also aliased the table MainFrameLink to make my typing
easier.

, IIF(IsDate(Format(MF.EffDt,"@@\/@@\/@@"))= False or Asc(MF.EffDt)= 32, Null,
CDate(Format(MF.EffDt,"@@\/@@\/@@"))) AS EffDt,


Hope this works for you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Good Morning,
I hope someone can help me before I drop-kick Office 2007 and make my IT
department put me back on 2003. **Note 2003 gave me no problems at all with
this. EVER!**
The section between ****'s is my sample data. Source is .txt file. Data is
fixed width. Import cannot recognize a negative number represented by the "-"
after a number value. Dates have been linked as text, as I cannot command the
system to ignore the #Num! error. The section between ++++'s is a sample of
the resulting link. The third section is my SQL, which works around the date
problem and the subsequent #Error problem. When I get down to the information
I need, there are still lines that I do not need, this line contains the word
description. There is now no date in the EffDt field, but when I try to
filter out records with NULL value, I get this error: Data type mismatch in
criteria expression. I am trying to append to or create another table.

QUESTION: How do I simplify this unwieldy query and/or remove the records
programatically? **Please do not respond with "correct the data first", as
with hundreds or thousands of lines, that is not time effective.**
****
26212 0003 C 1957 APT AIR CONDITIONIN 101.00 08-28-08 09-01-08
09-13-2008
26212 0004 C 1957 APT AIR CONDITIONIN 101.00 08-28-08 09-01-08
09-13-2008
26211 0002 C 1957 APT RENT 573.00 09-01-08 09-01-08
280.00
11005 0001 C ISS SPECIAL SERVICES FEE 10.00 09-11-08
0.00
11003 0001 C INTERNATIONAL STUDENT FE 50.00 09-12-08
0.00
26212 0005 C 1957 APT AIR CONDITIONIN 101.00- 09-12-08
09-13-2008
90000 0001 P PAYMENT ON ACCOUNT 400.00- 09-12-08
09-13-2008
90000 0002 P PAYMENT ON ACCOUNT 8,110.95- 09-12-08
09-13-2008
Page 8 of 9 Pages in Unit 1 Total Units 1 Next Page __ Next Unit
__
W-Z0137 - SECURITY - YOU MAY NOT UPDATE ON THIS SCREEN
Student Account Date: 09-13-2008
SubCd Seq CP Description Amount Eff Dt Bill Dt Pd
Appl
****** Future Items Begin Here *****
26211 0003 C 1957 APT RENT 573.00 10-01-08
0.00
26211 0004 C 1957 APT RENT 573.00 11-01-08
0.00
26211 0005 C 1957 APT RENT 573.00 12-01-08
0.00

End Balance: 2,072.00
****
++++
CP Description Amount Sign EffDt BillDt PdAppl
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT AIR CONDITIONIN $101.00 08-28-08 09-01-08 09-13-2008
C 1957 APT RENT $573.00 09-01-08 09-01-08 280.00
C ISS SPECIAL SERVICES FEE $10.00 09-11-08 0.00
C INTERNATIONAL STUDENT FE $50.00 09-12-08 0.00
C 1957 APT AIR CONDITIONIN $101.00 - 09-12-08 09-13-2008
P PAYMENT ON ACCOUNT $400.00 - 09-12-08 09-13-2008
P PAYMENT ON ACCOUNT $8,110.95 - 09-12-08 09-13-2008
Pages in Unit 1 To Next Pa e __ Ne t Unit __
E URITY - YOU MAY NOT UPDA C EEN

Student Account ate: 09- 3-2008
C Description Eff Dt Bill Dt Pd Appl
r Items Begin Here *****
C 1957 APT RENT $573.00 10-01-08 0.00
C 1957 APT RENT $573.00 11-01-08 0.00
C 1957 APT RENT $573.00 12-01-08 0.00

End Balance: $2,072.00

++++

SELECT MainframeLink.Description, MainframeLink.CP,
IIf([MainframeLink]![Sign]="-",-[MainframeLink]![Amount],[MainframeLink]![Amount])
AS Amount,
IIf(IsNull([MainframeLink]![EffDt]),"",IIf(Asc([MainframeLink]![EffDt])="32","",IIf(IsDate(DateSerial(Right([MainframeLink]![EffDt],2),Left([MainframeLink]![EffDt],2),Mid([MainframeLink]![EffDt],4,2))),DateSerial(Right([MainframeLink]![EffDt],2),Left([MainframeLink]![EffDt],2),Mid([MainframeLink]![EffDt],4,2)),"")))
AS EffDt,
IIf(IsNull([MainframeLink]![BillDt]),"",IIf(IsDate(DateSerial(Right([MainframeLink]![BillDt],2),Left([MainframeLink]![BillDt],2),Mid([MainframeLink]![BillDt],4,2))),DateSerial(Right([MainframeLink]![BillDt],2),Left([MainframeLink]![BillDt],2),Mid([MainframeLink]![BillDt],4,2)),""))
AS BillDt,
IIf(IsNull([MainframeLink]![PdAppl]),"",IIf(Asc([MainframeLink]![PdAppl])="32","",IIf(IsDate(DateSerial(Right([MainframeLink]![PdAppl],2),Left([MainframeLink]![PdAppl],2),Mid([MainframeLink]![PdAppl],4,2))),DateSerial(Right([MainframeLink]![PdAppl],2),Left([MainframeLink]![PdAppl],2),Mid([MainframeLink]![PdAppl],4,2)),""))) AS PdAppl INTO Mainframe
FROM MainframeLink
WHERE (((MainframeLink.Description)<>"BALANCE FORWARD") AND
((MainframeLink.CP)="C" Or (MainframeLink.CP)="P"));
Click to expand...
 
C

catkin63

Unfortunately, now it gives me no date at all. Perhaps I am misunderstanding
the "@@\/@@\/@@" by this you mean this literally is what I should type as the
format as in you example?
 
C

catkin63

I was finally able to figure a work around. Here is what I came up with:

SELECT MainframeLink.Description, MainframeLink.CP,
IIf([MainframeLink]![Sign]="-",-[MainframeLink]![Amount],[MainframeLink]![Amount])
AS Amount, IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)
AS EffDt, IIf(IsDate([MainframeLink]![BillDt]),[MainframeLink]![BillDt],Null)
AS BillDt,
IIf(IsNumeric([MainframeLink]![PdAppl]),Null,IIf(IsDate([MainframeLink]![PdAppl]),[MainframeLink]![PdAppl],Null)) AS PdAppl
FROM MainframeLink
WHERE (((MainframeLink.Description)<>"Balance Due") AND
((MainframeLink.CP)="c" Or (MainframeLink.CP)="p") AND
((IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)) Is Not
Null));
 
J

John Spencer

If you are getting no date at all then I obviously don't understand your
data. If your "Date" field looks like "123199" for Dec 31 1999 then
that expression should work.

Format("123199","@@\/@@\/@@") should return a string of 12/31/99. Then
IsDate should return TRUE that is a date. Does your field source have
trailing or leading spaces?

Try the following expression.

IIF(IsDate(Format(Trim([fieldwithSpaces]),"@@/@@/@@")),
CDate(Format(Trim([fieldwithSpaces]),"@@/@@/@@")),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Unfortunately, now it gives me no date at all. Perhaps I am misunderstanding
the "@@\/@@\/@@" by this you mean this literally is what I should type as the
format as in you example?

John Spencer said:
First ASC function returns a number 32 not a string "32"
Second try using . as the separator instead of ! between table and field names.
Third try the following to return a date field. It is shorter and will return
a date field not a text field. Whenever you use a string ("") as one of the
options to be returned, the field will be defined as a text field. Using Null
as an option allows you to return a date.

IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@")))

The formula above does rely on the date being in mmddyy form - which is what I
deduced from your code.

SELECT MF.Description
, MF.CP
, IIf([MF].[Sign]="-",-[MF].[Amount],[MF].[Amount]) AS Amount

, IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@"))) AS EffDte,


IIF(IsDate(Format(BillDt,"@@\/@@\/@@"))= False or Asc(BillDt)= 32, Null,
CDate(Format(BillDt,"@@\/@@\/@@"))) AS BillDte,

, IIF(IsDate(Format(pdAppl,"@@\/@@\/@@"))= False or Asc(pdAppl)= 32, Null,
CDate(Format(pdAppl,"@@\/@@\/@@"))) AS PdApl
INTO Mainframe
FROM MainframeLink AS MF
WHERE (((MF.Description)<>"BALANCE FORWARD") AND
((MF.CP)="C" Or (MF.CP)="P"));

Note that I slightly changed your expression aliases. If you wish to use the
same names you were using. you will have to fully qualify the field references
in the expressions. I also aliased the table MainFrameLink to make my typing
easier.

, IIF(IsDate(Format(MF.EffDt,"@@\/@@\/@@"))= False or Asc(MF.EffDt)= 32, Null,
CDate(Format(MF.EffDt,"@@\/@@\/@@"))) AS EffDt,


Hope this works for you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
C

catkin63

Unfortunately, while the fix looks good when I view the query in Datatable
form, when I use it to append to or make a new table, I get the following
error:

You cannot record your changes because a value you entered violates the
settings defined for this table or list (for example, a value is less than
the minimum or greater than the maximum). Correct the error and try again.

When I dig deeper, I find Error Number: 2950

catkin63 said:
I was finally able to figure a work around. Here is what I came up with:

SELECT MainframeLink.Description, MainframeLink.CP,
IIf([MainframeLink]![Sign]="-",-[MainframeLink]![Amount],[MainframeLink]![Amount])
AS Amount, IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)
AS EffDt, IIf(IsDate([MainframeLink]![BillDt]),[MainframeLink]![BillDt],Null)
AS BillDt,
IIf(IsNumeric([MainframeLink]![PdAppl]),Null,IIf(IsDate([MainframeLink]![PdAppl]),[MainframeLink]![PdAppl],Null)) AS PdAppl
FROM MainframeLink
WHERE (((MainframeLink.Description)<>"Balance Due") AND
((MainframeLink.CP)="c" Or (MainframeLink.CP)="p") AND
((IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)) Is Not
Null));


John Spencer said:
First ASC function returns a number 32 not a string "32"
Second try using . as the separator instead of ! between table and field names.
Third try the following to return a date field. It is shorter and will return
a date field not a text field. Whenever you use a string ("") as one of the
options to be returned, the field will be defined as a text field. Using Null
as an option allows you to return a date.

IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@")))

The formula above does rely on the date being in mmddyy form - which is what I
deduced from your code.

SELECT MF.Description
, MF.CP
, IIf([MF].[Sign]="-",-[MF].[Amount],[MF].[Amount]) AS Amount

, IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@"))) AS EffDte,


IIF(IsDate(Format(BillDt,"@@\/@@\/@@"))= False or Asc(BillDt)= 32, Null,
CDate(Format(BillDt,"@@\/@@\/@@"))) AS BillDte,

, IIF(IsDate(Format(pdAppl,"@@\/@@\/@@"))= False or Asc(pdAppl)= 32, Null,
CDate(Format(pdAppl,"@@\/@@\/@@"))) AS PdApl
INTO Mainframe
FROM MainframeLink AS MF
WHERE (((MF.Description)<>"BALANCE FORWARD") AND
((MF.CP)="C" Or (MF.CP)="P"));

Note that I slightly changed your expression aliases. If you wish to use the
same names you were using. you will have to fully qualify the field references
in the expressions. I also aliased the table MainFrameLink to make my typing
easier.

, IIF(IsDate(Format(MF.EffDt,"@@\/@@\/@@"))= False or Asc(MF.EffDt)= 32, Null,
CDate(Format(MF.EffDt,"@@\/@@\/@@"))) AS EffDt,


Hope this works for you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
C

catkin63

Of course, it does help in the macro to remove all references to the query
that was failing instead of the query that worked.

Mystery Solved. Thank you for your advice John Spencer. You set my thinking
Unfortunately, while the fix looks good when I view the query in Datatable
form, when I use it to append to or make a new table, I get the following
error:

You cannot record your changes because a value you entered violates the
settings defined for this table or list (for example, a value is less than
the minimum or greater than the maximum). Correct the error and try again.

When I dig deeper, I find Error Number: 2950

catkin63 said:
I was finally able to figure a work around. Here is what I came up with:

SELECT MainframeLink.Description, MainframeLink.CP,
IIf([MainframeLink]![Sign]="-",-[MainframeLink]![Amount],[MainframeLink]![Amount])
AS Amount, IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)
AS EffDt, IIf(IsDate([MainframeLink]![BillDt]),[MainframeLink]![BillDt],Null)
AS BillDt,
IIf(IsNumeric([MainframeLink]![PdAppl]),Null,IIf(IsDate([MainframeLink]![PdAppl]),[MainframeLink]![PdAppl],Null)) AS PdAppl
FROM MainframeLink
WHERE (((MainframeLink.Description)<>"Balance Due") AND
((MainframeLink.CP)="c" Or (MainframeLink.CP)="p") AND
((IIf(IsDate([MainframeLink]![EffDt]),[MainframeLink]![EffDt],Null)) Is Not
Null));


John Spencer said:
First ASC function returns a number 32 not a string "32"
Second try using . as the separator instead of ! between table and field names.
Third try the following to return a date field. It is shorter and will return
a date field not a text field. Whenever you use a string ("") as one of the
options to be returned, the field will be defined as a text field. Using Null
as an option allows you to return a date.

IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@")))

The formula above does rely on the date being in mmddyy form - which is what I
deduced from your code.

SELECT MF.Description
, MF.CP
, IIf([MF].[Sign]="-",-[MF].[Amount],[MF].[Amount]) AS Amount

, IIF(IsDate(Format(EffDt,"@@\/@@\/@@"))= False or Asc(EffDt)= 32, Null,
CDate(Format(EffDt,"@@\/@@\/@@"))) AS EffDte,


IIF(IsDate(Format(BillDt,"@@\/@@\/@@"))= False or Asc(BillDt)= 32, Null,
CDate(Format(BillDt,"@@\/@@\/@@"))) AS BillDte,

, IIF(IsDate(Format(pdAppl,"@@\/@@\/@@"))= False or Asc(pdAppl)= 32, Null,
CDate(Format(pdAppl,"@@\/@@\/@@"))) AS PdApl
INTO Mainframe
FROM MainframeLink AS MF
WHERE (((MF.Description)<>"BALANCE FORWARD") AND
((MF.CP)="C" Or (MF.CP)="P"));

Note that I slightly changed your expression aliases. If you wish to use the
same names you were using. you will have to fully qualify the field references
in the expressions. I also aliased the table MainFrameLink to make my typing
easier.

, IIF(IsDate(Format(MF.EffDt,"@@\/@@\/@@"))= False or Asc(MF.EffDt)= 32, Null,
CDate(Format(MF.EffDt,"@@\/@@\/@@"))) AS EffDt,


Hope this works for you.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
Click to expand...
 

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

Advanced problem using auto-number and datediff to find readmissio 2
I need to help with Excel (beginner) -- year row and month column 2
Subtotal Function 0
Please help for FV and XIRR function. 3
Payroll 2
Ranking, subqueries and update query problem 11
who can help please with table query ? 1
Outlook connector crashes outlook 0

Top