Dropping Leading Zeros? 2

[jt643]

I have a check_no_ field from SQL Server that is text. I am pulling it into Access via a query. From there, it will populate several different tables culminating in a report with the Check Number.

I want to find a way to make sure that on the Report the leading zeros (i.e. check_no_ = 000001510) to drop off and therefore only show the number.

I tried to change the field format for the tables to which I write the information from the query to 'Number', but since the original format was 'Text', I keep getting 'Data Mis-match' errors.

I also tried to just change the format of the check_no_ field on the report to 'General Number', but the leading zeros keep showing up.

Thanks for any advice in advance.

 

[Remou]

Hi
How about Val(check_no_ )?

 

[jt643]

Thanks Remou.

Where would I insert that? The query, the field format of the report...?

 

[Remou]

Either works fine. The query might be easiest.

 

[jt643]

Thanks Remou.

So, my 'Field' value in my Select statement would be Val(check_no_), and that would tell the SQL statement to return that field as a 'Value'?

 

[Remou]

Yes.

 

[jt643]

Remou - you have already earned a star.

I have had a brain cramp. My VB gives me an error on the following statement:

Set Report2Rst = Report2Db.OpenRecordset("select * from DrawTotals WHERE check_no_ = #" & ReportRst!check_no_ & "#")

I know it has to do with my syntax on the select statement. Since my check_no_ field is now a number (thanks to you!), what am I missing with my WHERE statement in terms of syntax?

Thanks so much for your help.

 

[PHV]

Get rid of the # (it's for date constants)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jt643]

Brilliant!

Thanks PH.