number in criteria changing to text

[Jennifer]

I have an update query with the following where statement:
WHERE (([SpecialtiesParsed]![Number]="1") AND
([SpecialtiesParsed]![Number]="1")) OR (([SpecialtiesParsed]![Number]="2")
AND ([SpecialtiesParsed]![Number]="2")) OR
(([SpecialtiesParsed]![Number]="3") AND ([SpecialtiesParsed]![Number]="3"))
OR (([SpecialtiesParsed]![Number]="3") AND
([SpecialtiesParsed]![Number]="3"));

The SpecialtesParsed!Number is a number field, not a text field.

The query works fine if I go to my SpecialtiesParsed Table and convert the
Number field to text, however, that is not a good solution, since I need the
data to be a number for other purposes.

I cannot seem to find a way to stop access from assuming this is a text
field when I type the criteria.

Any Ideas?

Any ideas?

 

[John Spencer]

That is strange.

I would suggest that you post the entire SQL of the query to aid in
diagnosis.

You should be able to use
WHERE [SpecialtiesParsed].[Number] in (1,2,3)

Note the use of the period as the separator between the tablename and
field name.

If SpecialtiesParsed is a query, you might want to post the SQL for it also.


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

 

[John W. Vinson]

I have an update query with the following where statement:
WHERE (([SpecialtiesParsed]![Number]="1") AND
([SpecialtiesParsed]![Number]="1")) OR (([SpecialtiesParsed]![Number]="2")
AND ([SpecialtiesParsed]![Number]="2")) OR
(([SpecialtiesParsed]![Number]="3") AND ([SpecialtiesParsed]![Number]="3"))
OR (([SpecialtiesParsed]![Number]="3") AND
([SpecialtiesParsed]![Number]="3"));

The SpecialtesParsed!Number is a number field, not a text field.

The query works fine if I go to my SpecialtiesParsed Table and convert the
Number field to text, however, that is not a good solution, since I need the
data to be a number for other purposes.

I cannot seem to find a way to stop access from assuming this is a text
field when I type the criteria.

Just remove the quotemarks from your criteria. Using a criterion

=1

is appropriate for a number field;

="1"

is appropriate for a text field.

Also change the ! to . - the exclamation point delimiter is appropriate for
form controls and the like, not for table field specification. It's not at all
clear why you're asking for the same criterion twice, either - a where clause

WHERE [SpecialitiesParsed] IN (1, 2, 3)

should work just fine.

 

[Jerry Whittle]

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

 

[Jerry Whittle]

Try removing the double quotes " " around the numbers. You're criteria is
saying look for the text character 3 and not the number 3. Usually Access is
pretty good about converting back and forth, but something must be causing a
problem.