I have an excel sheet column that when imported the value in the field looks like this 1, 3, 5. It is imported as text. I would like this 1, 3, 5 to be part of an In function In (1, 3, 5) in a query but the 1, 3, 5 needs to recognized as numbers. Any suggestions?

Thanks,

BB

if you are doing: where x in (1, 3, 5) these are numbers
if you are doing: where x in ('1', '3', '5') these are text

If you don't want to use the second method, then you could change your field definition from text to number and the first method would work.

That's kind of the catch. The data is stored in a text field [Location] as 1,3,5. The In function is looking at a number field, so if i do where x In ([location]) i get the data type mismatch error. I can convert the field i'm querying against to text. This gets rid of the data type mismatch but it doesn't pull any results. I suspect it's still in the way the data is stored, (1,3,5). I tried storing as ('1','3','5') but pulled no results. The field i'm querying against would have the data stored as 1 in a record, 3 in a seperate record and 5 in another.


Thanks,

BB

Try CInt([Location]) or CLng([Location]) depending on the data type of X.

I still can't seem to get this to work. Is there a certain way I can format the data in [location], i.e '1', '3' ( which isn't working either) but I thought there may some way to store it to get it to work with the In statement.


Thanks,

Buddy

Changing the data type to the one you needed or using Van's solution should have worked. Can you post a stripped down version of your db with the sql query that's failing? We need to see the table structure as well.

Here is a sample of what I'm trying to do. Query1 has the In function between 'Location' in tables A and B. TableA!Location has single instances of the group # with a single instance of location where Location is 1, 3, 5 for example, where tableB!Location could be multiple instances of the same group # but with single instances of the Location i.e group 100 Location 1, group 100 Location 3.... If this doesn't make sense hopefully looking at the tables and query will.

thanks,

BB

BB

A field should not store multiple values.

One field = one value.

yeah, I know. It's just the way I'm receiving the data and I was trying to take the easy way out. Actually the easy way would be revising the incoming data, I was just hoping I could do an In function work around.


Thanks,

BB

BB

If the data stays as it is I think it's probably wildcards rather than In
you should be looking at.

But if you do want to change the data then that could probably be done with
some simple code.

BB,
Your table A is not normalized - specifically your location field. That is why you are having issues. It would be in your best long term interest to normalize your tables, especially since your tables currently hold almost no data. If you go with a workaround now, then this will be more difficult to fix later on (when you have more data). It will also make it more difficult to maintain and it will make it significantly more difficult when you ask for help. Do you need help with the normalization process?

Ender

The table won't get any bigger (it has more data now, I just uploaded an example), each time new data is imported the old data is deleted- I don't need the history. Here's the deal, this data is being received via an excel sheet from another area, that's the main reason the data appears as 1,3,5 in the location field. This works for me if I could get the query example (query1) to work as is. The data will continue to be sent to me via an excel sheet.
The sender will not won't to enter the same information over and over with only the location being different, i.e group A Location 1, group A location 3. I can possibly reformat the sheet to have a column for each location with a y/n or checkbox format. I was just trying to work with what I've been receiving and thought there might be a simple code someone knew to make it work.


Thanks,

BB

See the new Query I created in the attached zipped MDB file.

Thank You Van. I believe that's what I needed. I know the table structure isn't as it should be, but in this case I just needed to work with what I had.

Thanks Again,

BB

Sometimes, we have to work with existing structures ... I look after one database with wrong Table Structure ( + terrible GUI set-up) written by a previous employee but the client only wants me to patch things up rather than re-design the entire database application.

You're welcome ... Glad to help ...