trouble starting code

[Robeen]

I created a quick table with a Text field named 'Test' in which I put the numbers 1 - 15.

Then I created a query with this SQL:

 
SELECT Max([Test]) AS Max_ID
FROM Table3
Where cint(Test) < 10;

The CInt function converts the Text representation of a number into an actual number.

The query returns "9".



I think when you have a field that is going to display numbers - it should be defined it in the table as Numeric.
It just saves contortions like using the CInt() finction in the query.

Still - I hope this helps!!

Let me know if if this doesn't work for you.

[mejia.j88]

I'm getting a datatype mismatch in criteria expression when i try to run the qry with the suggested SQL,

the data type of [cut_no] is set to text

[Robeen]

Could you post your SQL here, please?

[mejia.j88]

perhaps i need to write some sort of vba code like:

Dim Final cut, MyInt
Final cut=11 'Final cut is the eleventh cut
MyInt=Cint(Final cut)

[mejia.j88]

SELECT Max([cut_no]) AS Max_ID
FROM tbl_dicing_yield
Where cint(cut_no) < 10;

[Robeen]

That looks like the exact SQL that my query has.
I'm not sure why mine works and yours doesn't.

Any chance you can post your Database here - or even just a stripped down version of it?

[mejia.j88]

attached as zip

[mejia.j88]

woops, here it is.

[Robeen]

1.
tbl_Dicing_Yield is linked - so none of the data came with the DB.
And I can't look at anything to do with it because it is linked to something that is outside the DB you uploaded.

2.
Where can I see the SQL you mentioned above?
Is it in a Query? If so - which Query?

[mejia.j88]

Sorry,
every database is linked to another.
i attached a photo of what the information should look like, [blade_no] entries vary as Fxxx,Rxxx,Bxxx (xxx are numbers) and have corresponding blade cuts from 1-10 and Final cut under [cut_no].

the SQL should be under the query qry_blade_no_order


SELECT Max([cut_no]) AS Max_ID
FROM tbl_dicing_yield
Where cint(cut_no) < 10;


if you need the actual DB i'll see if i can post it, it's under company-confidentiality so i'll try to get permission.

[Robeen]

if there's some way you can make a copy of that table [import it into your database] and then upload the db again - that would enable me to debug.

qry_blade_no_order has this sql:
SELECT tbl_Dicing_Yield.Blade_No, tbl_Dicing_Yield.Cut_No
FROM tbl_Dicing_Yield;

[mejia.j88]

hopefully this should be it.

the SQL that is on there is because i needed something to work while i figured this problem out.

thanks so much.

-jm

[mejia.j88]

it is attached.

[Robeen]

Mejia,

My approach will not work.

That is because in your Cut_No field - not only is the data type of the field Text . . . but you also have Words [like "First Cut" . . . and "Final Cut"] in the field.
You cannot apply the CInt() Function to a field if there are values in there that cannot be converted to Numeric.
Eg: You cannot convert "First Cut" to a number.
When I did my own test, I DID have a Text field - but I only had numbers in the text field.

If you MUST have values like 'First Cut' and 'Final Cut' in the Cut_No field
. . . then . . .
I think you will have to revert to creating a Function [with VBA code] to manipulate what is in that field.
Are you comfortable with creating a function to use in your Query?
Have you done that before?

[mejia.j88]

I was thinking something like:

private function numbertotext()
Dim Final cut, MyInt

Final cut=11 'Final cut is the eleventh cut
MyInt=Cint(Final cut)

end function

put this under

Private subroutine txt_cut_no_afterupdate()
call numbertotext
docmd. openquery "qry_blade_no_order"
end sub

this will assign a value to all the "final cuts" and then allow the sql you gave me to work.

...i think?
there might be some info missing in the code.