Searching for consequential numbers in a dataset

[Sharkman1885]

unfortunately with compression and removal of my other forms and queries as well as a removal of over 200 entries I can only get the compressed file to 34 MB

 

[Minty]

Have you tried to compact and repair your database? That's pretty big for a stripped out file.

 

[Sharkman1885]

Minty thanks!!! Had no idea I could do that! ok here is the bare minimum of my data. The croc search form has the various means of searching. Im only concerned with the S, DL and DR fields right now.

 

[CJ_London]

The problem with doing this is that the data for the animal is all in one line

sorry - that is Excel thinking, not database. Access is not a bigger version of Excel.

it doesnt seem to work

doesn't help - and why strip out the mid function unless your data does not include 'S:'? and is just the numbers and slashes?

You have now added to the pattern, making things even more complicated and appear to have another version of the search string (1/x).

This thread is now suffering from a major case of 'mission creep' and from my perspective appears to be trying to work with something which is not designed to be easily interogated. So I will now butt out until a full and clear explanation of the data and requirement is provided - provide table structure, example data and the expected outcome from that data.

 

[Minty]

@CJ - The OP has now provided a sample table - and it appears to have the data available in a couple of formats in each detail line.

Not sure it helps too much in the end.

 

[Sharkman1885]

CJ I have attached a sample of the data. The data in the table is only numbers and slashes. The end goal is to be able to input part or all of the scute pattern and return the animal(s) that have that pattern and all pertinent information for those animals.

 

[CJ_London]

OK - I've taken a look.

Why are you using memo (long text) fields for S, DL & DR?

I just inserted the revised code and it works OK - just copy and paste this

And (("/" & Crocodile_ID. & "/") Like "*/" & replace(Forms![Search Crocodiles]!S,"/","/*") & "/*" Or Forms![Search Crocodiles]!S Is Null)

on the old code, if you just enter 2, you will return crocs with 4/10/12 etc

on the revised code you will just get those with 2

 

[Sharkman1885]

CJ null values are still throwing an error. then again I havent utilized the nz function yet. Thank you so much for your help and I am sorry if I am being a pain. This is literally month 2 of me using Access.

 

[Mark_]

CJ null values are still throwing an error. then again I havent utilized the nz function yet. Thank you so much for your help and I am sorry if I am being a pain. This is literally month 2 of me using Access.

And this is exactly why you need to use NZ. NZ says "OK, if its not null return the first value, but if it IS null give them something they can use so they don't get errors"...

 

[Sharkman1885]

Mark, having not used that function before I am unsure where to put it in the code. Ill do some research and try to figure it out

 

[jdraw]

Just to say I agree with the others that your table is not designed for database.

Do you have a defined requirement with explicit conditions to identify what you need for output?

Is there a plan to redesign the application?

 

[Sharkman1885]

@jdraw I agree the table is not well designed. Again I inherited the database but the database is a work in progress. I have full access and can modify things that need to be. The form should allow someone to enter what they know about the crocodile(s) scute markings, tag number and the output would be all those animals that match. I'm not sure if I am answering your question though :/

 

[CJ_London]

I am unsure where to put it in the code

see post #17

However I did not have a problem so wonder if it is an issue somewhere else - you have pretty much duplicate code for other fields

 

[Sharkman1885]

Thank you EVERYONE! all of the help is much appreciated if I have any other unrelated issues arise then Ill make another thread. This was the final working code.

And (("/" & Crocodile_ID.[S] & "/") Like "*/" & replace(Nz(Forms![Search Crocodiles]!S,""),"/","/*") & "/*" Or Forms![Search Crocodiles]!S Is Null)

Again thank you everyone!

 

[jdraw]

Sharkman1885, Glad you have current issue resolved. If you are going to convert what you have to normalized table and relational database, here is a link to a lot of relevant info.

Good luck.

 

[Sharkman1885]

@CJ yeah I wasnt sure what i was going to have to make the form search for in the database so I coded one at a time and basically copied it and replaced variables as they needed to be replaced. Thanks again for all of your help. My next goal is to clean up the database a bit and make it easy for those entering data in my input form which is working ok so far

 

[Sharkman1885]

@jdraw Thanks!! and thank you for the link. Ill definitely look into that.

 

[Mark_]

Sharkman, any time you reference a variable that COULD be null.

As an example, if your code is

If Me.Txt_SharkmanIsGreat = TRUE Then

would be

If NZ(Me.Txt_SharkmanIsGreat,TRUE) = TRUE Then

This way your not testing a value that could be NULL when you expect a value of, say 0 or "".