if function

markdooler

Member
Local time
Today, 23:25
Joined
Nov 25, 2020
Messages
58
Hi ALl

I have converted a spreadsheet i use to be a database as it is growing large and unmanageable in spreadsheet form.

The issue i have is some of the functionality i get from the spreadsheet i cant seem to replicate in the database.

This is probably largely down to my lack of knowledge in Access compared to Excel.

Here is my issue.

I have 1 table called "day sheet", there is a field here called WIP number and a check box/text box field called "complete?"

I have another table called RB5 that only has WIP number in it.

I download the WIP number data into the RB5 table on an adhoc basis.

Want i want to do is to get the Day sheet table, complete field to use the WIP number field, go and look at the RB5 table, WIP number field and tell me if it is there or not.

For reference i have these same sheets set up in excel and use =IF([@[Wip Number]]="","",IF([@[Wip there?]]=[@[Wip Number]],"No","Yes"))

So it looks at the WIP number on the current page, looks at the RB5 page an returns a "No" if it can find it and a "Yes" if it cannot find it. (Yes this is backwards for a reason).

Any ideas would be great.

Thanks

Mark
 
if [@[Wip Number]] is your field name I would get rid of the special character and space.

Are you sure it = a zero length string? try using Nz(WipNumber,"") = ""
Thanks for that.

That code is just my example of how i do it in my spreadsheet.

Im not sure how to apply this to access.

sorry i have not used access for over 10 years so am extreemly rusty lol.
 
I was adding the link while you posted Take a look at iif() in link.

Also there is a big difference between a spreadsheet and a DB. Make sure you normalize your tables.

The more I read your question the more confused I get.
Could you post a sample?
 
Last edited:
I was adding the link while you posted Take a look at iif() in link.

Also there is a big difference between a spreadsheet and a DB. Make sure you normalize your tables.

The more I read your question the more confused I get.
Could you post a sample?
Thanks

so the picture below shows the design view of my table

the Wip Closed field is the one i want to go and look to see if the wip number appears in the rb5 wip number field (they have a relationship).

Where would i write the code and what might it be?

1606307715486.png
 
Before you get to deep into this you should work on your tables first.

You have fields Date In, Wip Closed?, Date Started, Paperwork Recieved (Workshop ...
You should avoid spaces in object names.
Some words such as Date and Name are reserved words.
With spaces and reserved words you'll have to bracket everything and it gets ugly and error prone.
Use names like DateIn And DateOut or CustomerName
Dont use special characters like ( or ?

Pick a naming convention and stick to it. I prefer camel case or pascal personally ... https://medium.com/better-programmi...amel-pascal-snake-and-kebab-case-981407998841

http://access.mvps.org/access/general/gen0012.htm

reserved words - https://regina-whipp.com/blog/?page_id=465

Another thing you should google is normalization.
 
Before you get to deep into this you should work on your tables first.

You have fields Date In, Wip Closed?, Date Started, Paperwork Recieved (Workshop ...
You should avoid spaces in object names.
Some words such as Date and Name are reserved words.
With spaces and reserved words you'll have to bracket everything and it gets ugly and error prone.
Use names like DateIn And DateOut or CustomerName
Dont use special characters like ( or ?
ok so removed as suggested

1606310205109.png
Happy with normalisation but well dive deeper into it.

I have also dealt with the paperwork received and removed the special characters and spaces

Next steps?

again, thanks for the support
 
Sorted lol. managed to work it out myself.

Again, thanks for the help
 

Users who are viewing this thread

Back
Top Bottom