Office - messages #15895

Office

Messages

Prospero Blocks

Hot Searches

NASA

Chat Center

Board Folders

	MS Office - Ge... 4351 msgs in 879 dscns Latest: Nov-13
	Outlook 10259 msgs in 1962 dscns Latest: 8:53 PM
	Word & Word VBA 9368 msgs in 1767 dscns Latest: Nov-18
	Excel & Excel VBA 12847 msgs in 2265 dscns Latest: Nov-17
	Powerpoint 750 msgs in 196 dscns Latest: Sep-18
	Access & Acces... 5436 msgs in 1214 dscns Latest: Nov-6
	Graphic Tools 130 msgs in 34 dscns Latest: Aug-25
	Publisher 254 msgs in 47 dscns Latest: Oct-18
	Works Suite 360 msgs in 73 dscns Latest: May-26
	Money & MSTaxS... 97 msgs in 27 dscns Latest: 12/25/08
	MS Maps & Trips 78 msgs in 15 dscns Latest: Oct-23
	FrontPage 1259 msgs in 224 dscns Latest: Oct-15
	Other Office P... 325 msgs in 82 dscns Latest: Oct-29
	OneNote 144 msgs in 19 dscns Latest: Nov-11

Message Area

To list

New

Access & Access VBA

String Conversion

Print Discussion

#1 of 6 Posted Nov-4 3:20 PM

From Carl Seiler/TX Posts 109 Last Nov-13
To All [Msg # 15895.1 ]

I'm trying to pull data from two different sources, both spreadsheets. One is an Excel file, the other is a spreadsheet created from data from the bank which I have saved as another Excel File. The two have rows which can be matched on check number after using the RIGHT function on the column from the bank that includes the check number. In order to match the two, I have to get the data types to match. As strings, I can match them up, but if I want to base my query on a comparison (e.g., check_no > 1500) I have to convert the check_no column from string to an integer.

The data for the check number comes in as text, and I can't change the data type as there are also blanks, nulls, and "n/a" in that column.

Query1:

SELECT qryExpensesNew.trans_date, qryExpensesNew.trans_chkno, qryExpensesNew.Draw_Date, qryExpensesNew.Amount, qryExpensesNew.report_param_2, qryExpensesNew.report_param_1
FROM qryExpensesNew;

where qryExpensesNew is the subquery that merges my two spreadsheets (tblTrans and tblExpenses) based on the parameters entered by the user at the query's runtime:

SELECT Format([Enter Draw Start],"Short date") & " through " & Format([Enter Draw End],"Short Date") AS report_param_1, [Enter Notes] AS report_param_2, tblTrans.trans_date, tblTrans.trans_chkno, tblExpenses.*
FROM tblTrans RIGHT JOIN tblExpenses ON tblTrans.trans_chkno = tblExpenses.Check_No
WHERE (((tblExpenses.Data_Type)='Exp') AND ((tblExpenses.Draw_Date) Between [Enter Draw Start] And [Enter Draw End]));

This runs fine. It's a right join as I want all of the transactions in the one table even if there's not a match in the second. So now I want to find all checks over a certain number and modify Query1 to read:

SELECT qryExpensesNew.trans_date, qryExpensesNew.trans_chkno, qryExpensesNew.HUD_Draw_Date, qryExpensesNew.Amount, qryExpensesNew.report_param_2, qryExpensesNew.report_param_1
FROM qryExpensesNew
WHERE CInt(Check_No) > 1500;

My end goal is that I really want to be able to get the check range from the user and find all checks that don't have a date in column tblTrans.trans_date as these would be checks that show up on the company's spreadsheet but haven't cleared the bank, and require somebody (i.e., me) to go research. But when I run this query, it says "The expression is typed incorrectly, or is too complex to be evaluated..." I'm guessing it is choking on the values that aren't convertible to integers. The Microsoft site says at http://office.microsoft.com/en-us/access/HA012290181033.aspx that if the value passed is outside the range of the data type being converted to, an error occurs. When I've experimented, I find that indeed, I get #Error for all of these fields. So basically, I think I need a query that is smart enough to only try to convert to integer if it's possible to do so, but I don't know how to do that, and if there's a better way to approach this whole thing.

Summary: qryExpensesNew.CheckNo has data type text, but I need to only get those rows which have valid numbers and/or pull out a range of numbers.

Thanks,
Carl

Options

#2 of 6 Posted Nov-4 6:48 PM

From Bill Manville Posts 4150 Last 4:00 PM
To Carl Seiler/TX [Msg # 15895.2 15895.1 ]

In some cases, string comparison would be fine
Check_No>'1500'
If Check_No is always 4 characters with leading zeros then it would work fine, but if Check_No could be '999' then that would satisfy >'1500'

Check_No>'1500' And Check_No<'1601' would work fine if Check_No is always 4 characters

Len(Check_No)=4 And Check_No>'1500' And Check_No<'1601' would work fine if Check_No could have various lengths but you only wanted 4-character ones.

Bill Manville
Excel MVP/TA
Oxford, England

Options

#3 of 6 Posted Nov-5 6:20 AM

From Carl Seiler/TX Posts 109 Last Nov-13
To Bill Manville [Msg # 15895.3 15895.2 ]

Thanks for the ideas, Bill. I'd always tried to avoid string comparisons on fields that are really properly numbers but happened to be in a text field. Generally, the numbers are all four digits, but I don't know if that will stay that way (in the future I'd hate for this to break down and me have to figure out why it doesn't work-but I think that could be so far in the future, that I won't be the one worrying about it). I'm thinking if the rare occasional four character text string shows up in there, I can just handle that manually.

Options

#4 of 6 Posted Nov-5 10:19 AM

From Carl Seiler/TX Posts 109 Last Nov-13
To Bill Manville [Msg # 15895.4 15895.2 ]

OK, here's what I've got right now, and it seems to work nicely. There's always the possibility that the one spreadsheet could come in with a four-character string instead of a check number, but no one has done that yet, but if I have a one-off "none" instead of the "n/a" and nulls (or blanks) that are currently in there, then I can handle those on a one-on-one basis. This will now give me all uncleared checks (trans_date Is Null) and save me a bunch of time later.

Sometimes I just get things in my head--gee, I can't compare these, they're strings not numbers. I need to get them converted--but often the solution is simpler than I make it in my head. Thanks, Bill.

SELECT qryExpensesNew.trans_date,
       qryExpensesNew.trans_chkno,
       qryExpensesNew.Check_No,
       qryExpensesNew.Draw_Date,
       qryExpensesNew.Amount,
       qryExpensesNew.report_param_2,
       qryExpensesNew.report_param_1
FROM   qryExpensesNew
WHERE  qryExpensesNew.Check_No >= [Start with check number]
       AND qryExpensesNew.Check_No <= [End with check number]
       AND trans_date IS NULL
       AND LEN(qryExpensesNew.Check_No) = 4;

Options

#5 of 6 Posted Nov-5 6:43 PM

From Bill Manville Posts 4150 Last 4:00 PM
To Carl Seiler/TX [Msg # 15895.5 15895.4 ]

Pleased to have simplified the problem - I often find that when I go back to something I wrote earlier I can see a better way to do it - such is the process of learning.

Incidentally you won't pick up any 'none' or similar because they will not be alphabetically between the 2 limiting check numbers.

Bill Manville
Excel MVP/TA
Oxford, England

Options