if formulas in access

[murray83]

hi

bit new to access and wondered if you could help, have been using excel for data storage but got to big so have moved to access, but in excel we had a formula where it checked if cell either contained GM or BWS and depending on what it was the customer number was either 3 or 4 charters long

this is the excel code

=IF(F884="GM",MID(C884,11,4),IF(F884="BWS",MID(C884,2,3),""))

have tried my best to write it into access but not sure if you can here is my attempt

=if Type="GM" then Customer=(order Number,mid,11,4) else if Type = "BWS" then Customer = (Order Number,Mid,2,3)

the Type, Customer and Order Number are fields

thanks for any help

 

[murray83]

ok have googled a bit more and am trying this in the expression builder

=[Customer] «Expr» IIf([Type]="GM", [Order Number] String(11,4), [Type]="BWS" [Order Number] String(2,3)

and it keeps saying this

 

[plog]

Before we begin, in access it matters where you put your code. The = at the front of your code implies its being used in an unbound control of a form or report. However, the action you want to take inside your code is the setting of the value of Customer, which implies this is in a module (since this is the only place you can set the value of something). So please clarify the context of your code--where are you putting this?

Next, in Access you should avoid non-alpha numeric characters in table and field names, otherwise you need to escape your fields with brackets [Field Name With Spaces]. That means you need to do that with Order Number (although I recommend changing it to OrderNumber).

With all that said, this is the link to an Iif statement: https://www.techonthenet.com/access/functions/advanced/iif.php

which is what you would use if this code is anywhere but a module (inside a module, you just use If).

 

[murray83]

OK have changed the name to OrderNumber and removed the extra = as below but still same error

= [Customer] «Expr» IIf([Type] GM, [OrderNumber] String(11,4), [Type] BWS [OrderNumber] String(2,3)

and yeah i want the customer to be a number depending on what the type is and pull the customer from ordernumber

slight edit here is my example

 

[moke123]

not quite sure i understand what you want but i believe you want the mid() not the string()
from what i can tell from your sample data you want the next 4 digits beginning
at the 11th character

 Mid([ordernumber],11,4)

 

[plog]

You've got a lot of things to fix in your database tables.

1. Naming. You have a lot of issues with the names you have given stuff:

1A. You fixed [Order Number], but you still have a ton of fields with spaces and non-alpha numeric characters.

1B. Reserved words. You should not name anything in access with a reserved word (https://support.office.com/en-us/ar...-symbols-e33eb3a9-8baa-4335-9f57-da237c63eabe). These are terms used in Access that can make coding and querying harder. Type and Name are both reserved words. You might have more

1C. Overly generic names. Type, Name and Data mean nothing. You should prefix them with what type, name and data they are (e.g. OrderType, CustomerName, InvoiceData).

2. Tables with only 1 real field for data (autonumbers are not real data--they are internal to the database) do not need to exist. That means your Type, Authorised By and Names tables should go. Instead you just store the value in the table where it will ultimately end up.

3. Invalid use of ID fields. You gave all those unnecessary tables ID fields, but then you turned around and just used the text values they corresponded to isntead of the ID value. When you do need an external table, you put an autonumber ID on the field, then in the external table you put that ID (that's the "relational" part of a relational database).

You really need to fix all of that before you proceed with this issue, because this problem while it might remain, will have to be solved in a different manner. So, I highly recommend you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) before you go any further with this database.

 

[murray83]

thanks for the info, I have changed some of the tables and such as suggested and have done some more tinkering with the the expression builder and I have this

=IIf([SKUType,GM],Mid([OrderNumber],11,4),IIf([SKUType,BWS],Mid([OrderNumber],3,2)))

this is almost working, it no longer says fault as before but the result it pulls into the customer just says #Name?

see attached image, any ideas

 

[isladogs]

This has several errors:

=IIf([SKUType,GM],Mid([OrderNumber],11,4),IIf([SKUType,BWS],Mid([OrderNumber],3,2)))

Try this

=IIf([SKUType]="GM",Mid([OrderNumber],11,4),IIf([SKUType]="BWS",Mid([OrderNumber],3,2),""))

or if SKUType can only equal GM or BWS then:

=IIf([SKUType]="GM",Mid([OrderNumber],11,4),Mid([OrderNumber],3,2))

 

[murray83]

works cheers

 

[murray83]

just one thing when i use the following code

=IIf([SKUType]="GM",Mid([OrderNumber],11,4),Mid([OrderNumber],3,2))

i get the gm showing data for instance the picture attached the customer should be 2716

any idea why :banghead:

 

[murray83]

also if you change from BWS or GM is access meant to update the customer or is that more programming

 

[isladogs]

Can you please upload the updated version of your db & I'll look at the 2 points you've raised

 

[murray83]

Can you please upload the updated version of your db & I'll look at the 2 points you've raised

of course and thanks

 

[isladogs]

Hi

I've spent a while looking at this with some success but there are lots of problems with the way you have created the database.

First of all, go through all of Plog's points from post #6 and act on ALL of them.

In addition,
1. All tables need a primary key field.
Currently the Data table doesn't have one - I've added an autonumber field DataID to this.
2. In the same table, the fields Day & Time should be renamed as e.g. OrderDate & OrderTime & both fields should be dateTime field type
3. In the 'Best' form, the cmbType uses a lookup from the what is a fairly pointless SKUType table. Instead just use GM or BWS in the combo box

There were also data errors e.g. no date for 2 records

There are other things but that's enough for now...

I then added an After_Update event with a Select case statement to update the Customer field
At this point, I got error 3061 (parameter missing) & decided it wasn't worth continuing until you have sorted out the basic database structure.

I realise there is still a lot of controls on the form that you haven't coded yet. Please sort the structure before you attempt to do so!

Attached is an updated version but you may not think its any improvement!

 

[murray83]

cheers for the input will get back to you all when have it fixed

 

[murray83]

ok started a fresh and i had it working but then i did something and it saved and now it bloody says #name again ahhhhhhhhhhhhhh

please see attached

shall read the reply's with the remains of my keyboard and mouse

 

[isladogs]

Hi

I've tried editing the GM / BWS control in each half of the form & it updates perfectly

However, some issues when I tried adding new records
e.g. Deallocation Time field is number type with an input mask - it should be DateTime formatted as short time e.g. 11:30
If you do that, you could add code so when the date is entered it automatically fills the time field using the Now() function

Not sure about the point of your 'switchboard' form frmStartup - assume you will add functionality to it later

 

[murray83]

do you put that in after update as expresion builder ?

and im preplexed that the customer worked for you, flipping stuff

could you save it then upload and see if it still works like magic

ta

 

[isladogs]

Yes and yes again. Will upload later this evening

 

[murray83]

ok cheers

i may have got the time thing down though as you can see in attached have done that and it puts time auto for the excat time now

thanks for the idea