Got a problem getting all the records for one individual through a query.
I have a query which prompts me to enter a name. (john jones)

The problem is that in the ODBC databse where the records are stored, they may be either just the name, or the name with an astrick in front.

A record might be
john jones or
*john jones.

What I need is to be able to get all of the records that contain john jones regardless of the asterick, but I haven't figure out how.:frust: :bonk: :frust:

HELP?????

James

Got a problem getting all the records for one individual through a query.
I have a query which prompts me to enter a name. (john jones)

The problem is that in the ODBC databse where the records are stored, they may be either just the name, or the name with an astrick in front.

A record might be
john jones or
*john jones.

What I need is to be able to get all of the records that contain john jones regardless of the asterick, but I haven't figure out how.:frust: :bonk: :frust:

HELP?????

James

Try using ?john jones.

Doesn't Access use wildcards, the *?

Could you do like *john jones or like john jones?

Also, you would want to use like, instead of =.

Use Like "*john Jones" in your search query. It works for me.

Sorry Re-read your post. Change your query to this: Like "*" & [Enter Person].

Sorry Re-read your post. Change your query to this: Like "*" & [Enter Person].

Thanks David I was beginning to think I didn't phrase the question right.

I tried your solution but it returned no records.

If I put in "*" & [Enter Person]
It returns the records with the asterick.

When I add the LIKE to the criteria it returns none at all.

James

Hi James,

I checked it again and it works 100% for me. I wonder if the difference is because of the ODBC link to the external table, or you have a different version of Access to mine - Access2003. I setup the complete example in Access. If you send me an email I can send you the small example i setup maybe this can help you to troubleshoot the problem.

David

If you can post the SQL I should be able to help.

Hi James,

I checked it again and it works 100% for me. I wonder if the difference is because of the ODBC link to the external table, or you have a different version of Access to mine - Access2003. I setup the complete example in Access. If you send me an email I can send you the small example i setup maybe this can help you to troubleshoot the problem.

David

Thanks David. I tried importing the table to eliminate the lindk and got the same result. I would appreciate a look at your example. I'll shoot you an e-mail momentarily.

I think the SQL is the key. There seems to be an issue, see http://support.microsoft.com/kb/163893

If you can post the SQL I should be able to help.


Here is the code, Maybe someone can find a way around this.

I looked at the topic posted by David and it appears to be written for windows 97. It does appear that the problem is with the linked table, although when I import the table I still get the same error.

Any further insight would be appreciated

James

***********************************

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY_DISP.[DISPOSED DATE]) Between [Enter Starting date as mm/dd/yy] And [Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

**********************************

Here is the code, Maybe someone can find a way around this.

I looked at the topic posted by David and it appears to be written for windows 97. It does appear that the problem is with the linked table, although when I import the table I still get the same error.

Any further insight would be appreciated

James

***********************************

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY_DISP.[DISPOSED DATE]) Between [Enter Starting date as mm/dd/yy] And [Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

**********************************

I don't see how you are prompted for a name from the SQL you have posted, the only input I would expect it to prompt you for is two dates.

Where did you the "between" command from? It's not one I have ever used and can't find reference to it either. Try the following SQL instead:

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY_DISP.[DISPOSED DATE])>=[Enter Starting date as mm/dd/yy] And <=[Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

Sorry,
I was trying some things and had taken it out. Try this one.

*************************************

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY.EMPLOYEE_NAME) Like "*" & [Enter Name]) AND ((V_QUALITY_DISP.[DISPOSED DATE]) Between [Enter Starting date as mm/dd/yy] And [Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

Are you using a form to enter the name or just entering it in a pop up box?

Try this:

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY.EMPLOYEE_NAME) Like "*" & [Enter Name] & "*") AND ((V_QUALITY_DISP.[DISPOSED DATE])>=[Enter Starting date as mm/dd/yy] And <=[Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

No dice. That did not work.
I am currently using a pup up box, but I would like to eventually do it with a drop down list.

I think I could do something with it if it were something other than the durn asterick.
I tried opening up the query with all names, copying it to excel and doing a find/replace but the asterick makes it pick everything.:frust: :frust: :frust:

James

When you removed the prompt for the name and just had the date inputs did your query return results?, which version of access are you using? and what type of database are the tables in?

Also try this:

SELECT V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT, V_QUALITY_DISP.QTY_DISPOSED, V_QUALITY_DISP.DISPOSED_VALUE, V_QUALITY.PART, [Discrepency Description].Description, V_QUALITY.[QUALITY DATE], V_QUALITY_DISP.[DISPOSED DATE]
FROM (V_QUALITY INNER JOIN V_QUALITY_DISP ON V_QUALITY.CONTROL_NUMBER = V_QUALITY_DISP.CONTROL_NUMBER) INNER JOIN [Discrepency Description] ON V_QUALITY_DISP.DISCREPANCY = [Discrepency Description].[Disc Code]
WHERE (((V_QUALITY.EMPLOYEE_NAME) Like "%" & [Enter Name] & "%") AND ((V_QUALITY_DISP.[DISPOSED DATE])>=[Enter Starting date as mm/dd/yy] And <=[Ending date mm/dd/yy]))
ORDER BY V_QUALITY.EMPLOYEE_NAME, V_QUALITY.EMPLOYEE_DEPT;

When you removed the prompt for the name and just had the date inputs did your query return results?, which version of access are you using? and what type of database are the tables in?

Yes. When the Name critera is removed it returns all the records for the given date range.

Version is 2003

Records are coming from a linked ODBC Database. (Importing the records has no effect on the query)

James

Did you try the sql in my previous post where I replace the *'s with %?

Did you try the sql in my previous post where I replace the *'s with %?

Yup.
Tried it both from an imported table and from a linked table.

Ok this is just weird one of those sql statements should have worked. Time to dust of the books.

Just tried copying your SQL statement directly into a new query and got a "Missing operator" error.

Don't know if that provides any light or not.

James

The missing operator is just because access uses * as it's wildcard character rather % which is generally used in most flavours of sql

Ok try using this in the sql:

'%' & [Enter Name] & '%')

Ok try using this in the sql:

'%' & [Enter Name] & '%')

Sorry chergh. Still doesn't work.
I'm beginning to think there isn't a way around this.

James

Sorry chergh. Still doesn't work.
I'm beginning to think there isn't a way around this.

James

James, if nothing here works out for you, you might want to try the MS Access newsgroups (http://www.microsoft.com/office/community/en-us/default.mspx?d=1). If you do, and you get an answer that works, post back here and tell us about it.

Yeah best go with what Jim suggested. I mocked up a table with names, some with * at the begining and some without, and can query them and get rows returned using quite a few different syntax combinsations. Your problem is puzzling. You could also try:

http://www.access-programmers.co.uk/forums/index.php

I've had a search and can't see any similar threads so you need to make a new thread about it, the community get annoyed when you post a new thread regarding a topic that has already been covered.

Yeah best go with what Jim suggested. I mocked up a table with names, some with * at the begining and some without, and can query them and get rows returned using quite a few different syntax combinsations. Your problem is puzzling. You could also try:

http://www.access-programmers.co.uk/forums/index.php

I've had a search and can't see any similar threads so you need to make a new thread about it, the community get annoyed when you post a new thread regarding a topic that has already been covered.

Yes I took Jim's advise. Started a new thread. Just bloody hard trying to give a good title. I'll let you know if I findout a solution.
Thanks for all your help and effort.

James

Hmmm. I do not have 2003, so I can't really test this... but you could try using a + instead of &, e.g. Like "*" + [Enter name]. I have seen this behave differently, but obviously I am not sure about the 2003 version.

Silly Girl

Both of the following formats should work:



SELECT HSK00023.Location, HSK00023.Oper, HSK00023.[Lot #], HSK00023.Qty, HSK00023.Comments, HSK00023.Gospod
FROM HSK00023
WHERE (((HSK00023.Location) Like "*" & "ABCDE" & "*"));


SELECT HSK00023.Location, HSK00023.Oper, HSK00023.[Lot #], HSK00023.Qty, HSK00023.Comments, HSK00023.Gospod
FROM HSK00023
WHERE (((HSK00023.Location) Like "*abcde*"));

A couple of observations:

First, a tip of the ol' chapeau to chergh, who in addition to helping out here also went over to the MS newsgroup and helped to explain the problem there. There still doesn't seem to be an answer to James's problem, but it certainly isn't due to lack of effort.

The other thing is an observation about database construction. Part of the problem here is that data fields have wildcard characters in them, which should never happen. It does happen because there are no standards, or there is insufficient discipline, for what's permissible. Once a database starts to grow, the importance of front-end control over data entry becomes extremely important. People who need information must be able to have some confidence that properly-constructed queries will have the expected results. Failing to establish rational conventions, and then enforcing them is a recipe for disaster.

A couple of observations:

First, a tip of the ol' chapeau to chergh, who in addition to helping out here also went over to the MS newsgroup and helped to explain the problem there. There still doesn't seem to be an answer to James's problem, but it certainly isn't due to lack of effort.

The other thing is an observation about database construction. Part of the problem here is that data fields have wildcard characters in them, which should never happen. It does happen because there are no standards, or there is insufficient discipline, for what's permissible. Once a database starts to grow, the importance of front-end control over data entry becomes extremely important. People who need information must be able to have some confidence that properly-constructed queries will have the expected results. Failing to establish rational conventions, and then enforcing them is a recipe for disaster.

IN absolute agreement Jim

Chergh has been a big help and I truely appreciate him, and all who have had suggestions.

Unfortunately I didn't have any control over that blasted asterick getting put on the records as that is part of a purchased program. It's being a real pain.

James

I just posted this over on the MS Access Newsgroups site:

***************************************
Well - - Gets curiouser and curiouser - -

For security reasons I changed the linked table to an imported table.
The Query still did not work.
I finally deleted the query all together and rebuilt it. - Now it works using the LIKE statement: LIKE "*" & [Enter Name] & "*"

I'll try it out over a couple of days and see how it goes.
Thanks for the help.

James:notme:

OK, here is a real low-tech answer from a guy who learned to program using Fortran and cards.

If we are just dealing with is there a leading asterisk or not, split the query logic into two or statements. Let us assume the field in question is 50 spaces wide.

WHERE [name] = "John Jones" or (right([name],49) = "John Jones" and left([name],1) = "*")

I can write a filter in Access using the left and right commands within the field definition.

OK, here is a real low-tech answer from a guy who learned to program using Fortran and cards.

If we are just dealing with is there a leading asterisk or not, split the query logic into two or statements. Let us assume the field in question is 50 spaces wide.

WHERE [name] = "John Jones" or (right([name],49) = "John Jones" and left([name],1) = "*")

I can write a filter in Access using the left and right commands within the field definition.

hmmmm Interesting solution - - I will take a look at that next time I run into this type issue.
thanks Steve

James

Failing to establish rational conventions, and then enforcing them is a recipe for disaster.Well said, I am a fanatic about "Limit to list" for data entry from Forms and using the Format, Input Mask, Validation Rule and Validation Text when creating Fields in a new table.- Now it works using the LIKE statement: LIKE "*" & [Enter Name] & "*"This is exactly the proper statement to return any data in the format "anything""john jones""anything". Hard to tell why it did not work originally. I use this syntax quite a bit to find things in a database that I have that includes leading spaces in the text entries. For example "space""john johns, jr." will only be found by "*john johns*" and not by "john johns*"