MS Access Query

My wife says to try changing the format of the "Pinned" field to text. Let me know results.

?ÿ

I gave it a shot after changing the "Pinned" field to Short Text data type, and it did not run any differently. And my SQL view looks the same still.?ÿ

Thanks for the idea. I am kinda new to access and kinda not liking it.?ÿ

?ÿ

Posted by: @ppm

I have a dateset of 1000's of projects. I am adding a new Field called Pinned. I want every Job # that contains an Asterisk to have a Yes (or True) value for the Pinned Field.?ÿ

Help an amateur access guy out please!

I believe I've got this.?ÿ PM me at lnelsonATcarlsonswDOTcom and I'll give you what I believe will work.

It's too late in the evening for me to tap it out on my cell phone.

I believe the * is a wildcard character.

So is the ?.

Try using something else.

My wife is working on your problem. She's an Access pro.

My wife says you can create a query that will return just the job numbers containing an asterisk without creating a pinned field. What's the purpose of the pinned field? It has been a very long time since she has worked with Access, but she's willing to help. I'll keep you updated.

If you want to select the records that have just an asterix, use the InStr() function with the Chr() function as the following:

SELECT [CLIENT DATABASE].[JOB #], [CLIENT DATABASE].PINNED
FROM [CLIENT DATABASE]
WHERE ((InStr(1,[JOB #],Chr(42))>="1"));

To update the PINNED field to True (Yes) from records in "JOB #" that contain an asterix, your query would resemble:

UPDATE [CLIENT DATABASE] SET [CLIENT DATABASE].PINNED = True
WHERE (((InStr(1,[JOB #],Chr(42)))>="1"));

To update the PINNED field to True (Yes) from records in "JOB #" that contain an asterix AND remove the asterix from the "JOB #" field, you would use the Replace() function and your query would resemble:

UPDATE [CLIENT DATABASE] SET [CLIENT DATABASE].[JOB #] = Replace([JOB #],Chr(42),""), [CLIENT DATABASE].PINNED = True
WHERE (((InStr(1,[JOB #],Chr(42)))>="1"));

In your query, you can right+click on the Query name and set the view from Design View to SQL View and paste the query string(s) above.

I hope this information helps.

@ladd-nelson?ÿ

Thank you Ladd. I will give it a try on Monday.

@field-dog?ÿ

Thank you for that suggestion.?ÿ

The asterisk is there to indicate the Job number has been "Pinned" in Google Earth. I am trying to get it in a separate field so it isn't in the job number field.?ÿ

If I can first query the asterisk, and Check (or 'Yes') in the pinned field, then...?ÿ I think I know how to remove the asterisk from the job number field.

?ÿ

I will try what Ladd suggested on Monday. Thank you for assisting.?ÿ

I will post an update if I get it.