Author Topic: Access 2003 Reports  (Read 797 times)

Offline mbaldw

  • Poaster
  • **
  • Posts: 345
    • Wildlife Online
Access 2003 Reports
« on: Jul 15, 2008, 02:25 AM »
Hi all,

does anyone know whether it's possible to create a query or report in Access (either XP or 2003) based on a search string?   I have an Access database containing several thousand articles and I want to generate a report showing all the articles that contain two or three words in the title.   All the tutorials I have found so far show how to generate reports based on specific categories in the database (so say, all those articles published in a particular journal in a particular year), but none mention using a specific search string.   Any ideas?

Cheers,
Marc.

Offline scuzzy

  • Forum Cop
  • Administrator
  • xTreme Poaster
  • *****
  • Posts: 4584
  • Caveat Empty: Beware of jesters
Re: Access 2003 Reports
« Reply #1 on: Jul 17, 2008, 09:52 AM »
Do you want to search titles that contain "x" number of words? For example, searching titles that contain a total of 3 words.

Or do you want to search titles for specific words, such as "taxes" and "report"?
Antec Performance TX640B Case | WinXP Pro SP3 & Win7 64-bit | Gigabyte GA-EP45-UD3R | Intel Core 2 Duo E8500 Wolfdale LGA 775 3.16GHz Dual-Core | 8GB (4x2GB) PC6400 G-Skill RAM | eVGA 7600GT 256MB PCI-E | 74GB WD Raptor SATA 16MB Cache | 74GB WD Raptor SATA 8MB Cache | 320GB Seagate Barracuda SATA 16MB Cache | External 640GB WD Caviar SATA 32MB Cache | Sony DRU-V200S DVD/RW | PC Power & Cooling Silencer 500W | Samsung SyncMaster 2494 (24") LCD Monitor | LG Flatron W2361V (23") LCD Monitor

Offline mbaldw

  • Poaster
  • **
  • Posts: 345
    • Wildlife Online
Re: Access 2003 Reports
« Reply #2 on: Jul 21, 2008, 06:30 AM »
Hi scuzzy, thanks for the response.   I want to search for records containing two different words.   For example, all the records where "apple" and "orange" are present in the "Fruit Name" field.   So, if in my fruit name field I had an entry that said: "Apples, oranges and pears were delivered to the Restaurant on Wednesday" it would get included in the results.

Do you know if this is possible?   If not, can it be done in Excel (I could always export the database to an XLS file if that would save me some aggro).

Cheers,
Marc.

Offline scuzzy

  • Forum Cop
  • Administrator
  • xTreme Poaster
  • *****
  • Posts: 4584
  • Caveat Empty: Beware of jesters
Re: Access 2003 Reports
« Reply #3 on: Jul 24, 2008, 05:22 PM »
I apologize for the delay in getting back to you.

To begin with, I recommend that you change the search default from "Fast Search" to "General Search". You'll find the settings under Tools>>>Options>>>Edit/Find. This way, Access will search for any word matching your criteria. If you leave it at the default setting, Access will only look for an exact "all or nothing" match. However, this setting only applies when doing a search from within a form.

You can create a simple formula for a query or report. I always ran reports off of a query. I have not used Access in about 5 yrs, but the search string in the query criteria will be as the following example (you must use the quotes and asterisks as shown):

Code: [Select]
Like "*apple*" Or Like "*orange*" Or Like "*melon*"

Running a query with that criteria, under the appropriate field, will yield all records with any of the quoted words anywhere inside the field. The asterisks are necessary to provide a wild search string. Otherwise the formula will only search for exact matches; not inclusive as you require.

The SQL for the above will look as follows:

Code: [Select]
SELECT [Table Name].Field Name
FROM [Table Name]
WHERE ((([Table Name].Field Name) Like "*apple*" Or ([Table Name].Field Name) Like "*orange*" Or ([Table Name].Field Name) Like "*melon*"));
« Last Edit: Jul 24, 2008, 05:32 PM by scuzzy »
Antec Performance TX640B Case | WinXP Pro SP3 & Win7 64-bit | Gigabyte GA-EP45-UD3R | Intel Core 2 Duo E8500 Wolfdale LGA 775 3.16GHz Dual-Core | 8GB (4x2GB) PC6400 G-Skill RAM | eVGA 7600GT 256MB PCI-E | 74GB WD Raptor SATA 16MB Cache | 74GB WD Raptor SATA 8MB Cache | 320GB Seagate Barracuda SATA 16MB Cache | External 640GB WD Caviar SATA 32MB Cache | Sony DRU-V200S DVD/RW | PC Power & Cooling Silencer 500W | Samsung SyncMaster 2494 (24") LCD Monitor | LG Flatron W2361V (23") LCD Monitor

Offline mbaldw

  • Poaster
  • **
  • Posts: 345
    • Wildlife Online
Re: Access 2003 Reports
« Reply #4 on: Jul 25, 2008, 03:41 AM »
Fantastic - thanks for that scuzzy.   I'll give it a shot this weekend.

Cheers,
Marc.