Problems with Replace function

[KirRoyale]

I have a field in an imported text file that should ideally be a number field but has preceding zeroes (e.g. 000000004800). I had to make this a text field on import as some of the fields have hyphens (-) in the middle (e.g. 0000000-9000), which meant that, if I selected a number data type, certain field values were thrown out.
My idea is to write a query and replace the hyphen with a zero and then to either remove the zeroes or convert to a number.
However, my initial step of replacing the hyphen with a zero
Expr1: Replace([FieldName],"-","0")
is giving me the error message of “data type mismatch in criteria expression”, which I do not understand as it is a text field and I have successfully used this in other text fields before.
Any help as to how to proceed would be appreciated.

 

[CJ_London]

From the error message, the error is in your criteria, not the replace function.

Can you post the complete sql to your query and also confirm that in the source table, FieldName does not (in fact cannot) contain any null values - if it does (or can), replace FieldName with nz(FieldName) as a first step.

For example, I have seen text files created (particularly from Excel) where there is a blank row between the header row and the first row of data.

 

[KirRoyale]

Thank you very much. As you said, there were null values in the field.
I used the Nz(fieldname) and that worked.
I do still have a couple of issues, though:
1. I am being prompted for parameter values for both the ‘Points’ and ‘NewPoints’ fields. Any input, including 1 letter makes this go away. How can I stop the parameter box from appearing?
2. In the ‘PointsNumber’ field, I have formatted the results as a number. This works ok, but I would like to have no decimal places. However, I do not know how to achieve this.
Could you please assist further? Query is posted below:
SELECT Call8CurrentTbl.電話番号（ＭＳＮ）, Nz([ポイント数]) AS Points, Replace([Points],"-","0") AS NewPoints, Format([NewPoints],"Standard") AS PointsNumber
FROM Call8CurrentTbl
GROUP BY Call8CurrentTbl.電話番号（ＭＳＮ）, Nz([ポイント数]), Call8CurrentTbl.[レコード区分], Replace([Points],"-","0"), Format([NewPoints],"Standard")
HAVING (((Call8CurrentTbl.[レコード区分])=2));

 

[thechazm]

KirRoyale if you open your query in design view and look at the ribbon at the top you will see a parameter button. Click on it and remove any entires there. That is why it's prompting you.

Also for no decimal places you might be able to get away with using the Clng function on your field which converts the numbers into a long which has no decimal places.

One other way is with the format command like you have already used above except do something like format([Field], "0") and see if that helps.

 

[KirRoyale]

Thank you for the Quick reply.
I clicked on the parameter button and there are no entries, so perhaps there is something wrong with my expression?
When I use:
PointsNumber: CLng([NewPoints])
I get the error message: 'The expression is typed incorrectly, or it is too complex to be evaluated…..’
What am I doing wrong?
If I change the query to:
PointsNumber: Format([NewPoints],"0")
It shows zero decimal places but also removed the thousand separators, which I would still like to have.
Would you be able to help further?

 

[thechazm]

I think you can do this format([Field], "000,000"). Try that and see if that helps.

Also to add another reason it's prompting for a parameter is one of your fields may have incorrect spelling of the field name or if you have a criteria set that is no longer applicable. Just look around and tripple check everything.

 

[KirRoyale]

Thank you again, thechazm.
format([Field], "000,000") unfortunately did not work. The results resorted back to the initial leading zero text formatting and gave results like:
020,450
I finally suppressed the parameter box (more by luck than judgement). However, there are a number of selections that seem to kick the parameter prompt into action. I cannot add totals or hide the interim points fields (‘Points’ or ‘NewPoints) without triggering the parameter prompt, which I don’t fully understand.
As things stand, I cannot make this into a totals query grouped on the phone numbers without triggering a prompt for the ‘Points’ field and the 'The expression is typed incorrectly, or it is too complex to be evaluated…..’ error message.
In the end, I created an additional totals query with just 2 fields: grouped on the phone number and totalled on points. This seems to work and the number does not have any decimal places. The only drawback is that I have not found a way of showing the thousand separators. So, I ended up with:
SELECT Call8CurrentTbl.電話番号（ＭＳＮ）, Nz([ポイント数]) AS Points, Replace([Points],"-","0") AS NewPoints, Format([NewPoints],"Standard") AS PointsNumber
FROM Call8CurrentTbl
WHERE (((Call8CurrentTbl.表示区分)=20 Or (Call8CurrentTbl.表示区分)=40) AND ((Call8CurrentTbl.[レコード区分])>=2));
and
SELECT Call8PointInfoQry.電話番号（ＭＳＮ）, Sum(Call8PointInfoQry.PointsNumber) AS PointsNumberOfSum
FROM Call8PointInfoQry
GROUP BY Call8PointInfoQry.電話番号（ＭＳＮ）;

 

[thechazm]

No worries after finally getting some sleep I remembered it was not supposed to be zero's in the field but pound signs. So it should have looked like format([Field], "###,###") which should work never the less I am glad you came up with a modified solution.

Good luck

 

[KirRoyale]

Thank you. I tried “###,###” (which was immediately converted to “#,###”) as well as “#,##0”. Both of these resulted in what seemed to be the required format e.g. 3,048.
SELECT Call8PointInfoQry.電話番号（ＭＳＮ）, Sum(Call8PointInfoQry.PointsNumber) AS PointsNumberOfSum, Format([PointsNumber],"#,###") AS Expr1
FROM Call8PointInfoQry
GROUP BY Call8PointInfoQry.電話番号（ＭＳＮ）, Format([PointsNumber],"#,###");
However, the ‘numbers’ are now left-aligned and this field cannot have a ‘sum’ in a totals query (I get the error message ‘data type mismatch in criteria expression’). So, it seems like the field is now treated as text.
Even more bizarrely, if I ‘sum’ in the totals query on the ‘original’ ‘PointsNumber’ field, the field with the “#,###” actually halves some (but only a few) of the values! 420 becomes 210!
I think I’ll quit while I’m ahead and put up with having no thousand separators!

 

[thechazm]

You don't want to use the thousands before your summing. After you sum then use the format on that and it will work. Ok good luck.