Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

microsoft access formula.

Aug 3, 2010 5:46PM PDT

I'm creating a database using Microsoft Access '97. Within a table I have one field which has a file number. The first 2 or 3 letters relate to the department such as 'OPS103' for operations for e.g. There are 8 departments in total and I'm wanting a seperate department field to be automatically updated when I run an update query. I realise I will need a formula which runs a wildcard and I have tried several formula with no success. My knowledge isn't great, so if anyone can help can you explain in basic terms. Any help would be greatly appreciated, thank you.

Discussion is locked

- Collapse -
Re: Access formula
Aug 6, 2010 6:50AM PDT

Can you make your problem somewhat more clear than you did now?
For example:
- What's a filenumber?
- Is 'OPS103' a department?
- If the filename is 'ABCDEF' is the department AB or ABC ("the first 2 or 3 letters")?
- Why an update query? Why not fill the separate field immediately if the original field is entered or changed?

And I wonder how you implemented the check for correctness of the filename entered. For example, if I would enter MA0102, how do you check if MA is a valid department?


And I wonder why you use Access 97? It's not supported on any later OS than Windows XP (i.e. Vista and 7).

The easy way to solve this seems to me to have 2 basic fields:
1. Department (including the correctness check), f.e. OPS
2. File number, f.e. 0102
Then all you need to derive the filename is a string concatenation. That's an easy formula.

Kees

- Collapse -
Also ...
Aug 8, 2010 4:50AM PDT

Try experimenting in Excel. Source string in column A, formula in column B. Functions to use are if, left (to take the left part of a string) and mid (to take a part of a string).
When it works in Excel, you won't find it difficult to do it in Access.

Kees

- Collapse -
Access formula
Aug 9, 2010 7:54PM PDT

Thanks for your response... I didn't explain my original problem very well. The data i'm working with is imported and the 'file number' that I refferred to is a standard number that is linked to each seperate file and the first 3 letters relate to a department (16 departments in total) and each department has approximately 20 files.

I've managed to make the database very user friendly so far, but if I could use some code or whatever to recognise the first letters of the file number, then automatically enter the department in a seperate field it would reduce workload and the potential for errors. It doesn't necessarily have to go into an update query but is essentially required to display in a report.

Unfortunately at work we are still running on '97, with a view to upgrading in the future. We are very much behind the times!!

I hope you have enough info to go on. Can't thank you enough for any help you can give!!

- Collapse -
Re: formulal in access
Aug 9, 2010 9:52PM PDT

Let me start with a note. Something like "OPS123" is NOT a number. It's an (alfanumeric) string. It might be that some people with you call it a number, but don't use that term when talking about it with others, like on this forum.
Talking about 'the first 3 letters of a number' definitely is uncommon.

If you need the first 3 characters of a string, use the formula left(string,3). And, according to this revised spec, that's all you need ("the first 3 letters relate to a department").

That's not fully true, of course, but my interpretation of your previous post. After all, the first three letters in the string "a12.23.Bdc" (which is a valid filename) are "aBd", while the first 3 characters are "a12". That's another example of being careful when writing specifications. If your ambition is to become a good programma, a necessary first step is being clear about what a program should do.

Kees

- Collapse -
(NT) Typo. Programma should be programmer.
Aug 10, 2010 5:10AM PDT
- Collapse -
Access formula
Aug 13, 2010 1:29AM PDT

Excuse my terminology, I'll never make a programma or a programmer for that matter!!!

My solution was placed directly into the report as follows:

IIf(LEFT([File No],2)="Op","Operations",IIf(LEFT([File No],2="HR","Personnel"))

Obviously the real formula goes on for 8 departments, therefore requires a closing bracket for each department, but you get the general idea.

Thanks for your help your a true hero!!

- Collapse -
(NT) Good job. Glad I could help.
Aug 14, 2010 1:19AM PDT