Only taken 1st two letters of a field.

[louisa]

Hi All,

On my contacts page within my db i have a field called postcode, the object of this exercise is i am trying to get access to create an account number for me.
I want the first 2 letters of the account number to be the first two letters of the customer postcode, i then want access to randomly generate 6 numbers and then i want the last 6 numbers to be the date of the customers first order.
I will hopefully be able to grab the first two letters and date but i want the 6 numbers to be randomly generated in the middle. I think i know how to get the randomly generated numbers and grab the date but i dont know how to specify to only grab the first two letters of a field?
Any ideas?

 

[vbaInet]

and then i want the last 6 numbers to be the date of the customers first order.

This bit doesn't sound like a feasible idea to me. What if the customer has never placed an order?

 

[louisa]

If the customer has never placed an order then no details will be on the db for them.
Once a customer has placed an order there details will be entered onto the db so they will then need an account number.

 

[vbaInet]

Bear in mind that an order has to be placed before a date is generated, which of course the account number is dependent upon.

 

[louisa]

Hi, i have that in mind, when they place an order they will sign an order form, that comes to me and i enter the info on to the db. They will have placed an order and i will have all contact info, order details etc.

 

[louisa]

No details are ever entered on to my db unless they have placed an order. If they havent placed an order they are not entered as they are not a customer.

 

[vbaInet]

I think you're missing my point. The steps of a new order are as follows:

1. Add a new customer - a customer account number should get generated here
2. Create a new order - but can't because you're missing the account number

For subsequent orders, yes it's possible.

 

[Brianwarnock]

Do you mean the first 2 numbers or characters, if you are in the UK and it is numbers what about L1 6BW as opposed to L16 5FY. If its characters then its simply the Left function, else you will need to write a function checking for numerical values.s

Brian

 

[Brianwarnock]

I think you're missing my point. The steps of a new order are as follows:

1. Add a new customer - a customer account number should get generated here
2. Create a new order - but can't because you're missing the account number

For subsequent orders, yes it's possible.

I think the point is that those 2 actions are concurrent, no order no customer.

Brian

 

[louisa]

Hi, i would need to have the first two characters, L1 or LE is fine, i just need the first two.
The business i am in a customer will place one order and that is it and will be billed monthly for that order alone, when i get their order i will enter their contact information and the order they placed. On the last page i want it to grab the first two characters of the postcode (which is on contacts page) then it to randomly generate numbers between 0-9 and then it will grab the order date which is also on another page and it will then provide me with an account number that i can put on the customers monthly bill.
This is what i need if it cannot be done i understand.

 

[louisa]

If i have a customer i have an order, if i dont have a customer i dont have an order.

 

[vbaInet]

I think the point is that those 2 actions are concurrent, no order no customer.

Brian

On paper yes, no order no customer. When putting this info into the db you will enter the Customer first followed by details of the order (for new customers with an order).

If the tables are properly normalized you will have three tables:

Customer
OrderHeader
OrderDetails

... in that order.

For new customers, you would have to create a customer account before adding details to the OrderHeader.

The way it will work is if:

* There is an Autonumber ID in the Customer table and a separate Customer Account Number field. This Autonumber ID will link to the OrderHeader so it won't matter if Acc Number field is left empty initially.

 

[louisa]

There is an Autonumber ID in the customer table and also a seperate customer account number.

 

[vbaInet]

Look into the following and see what you come up with:

Left(), DLookup()

DLookup() to get the postcode field corresponding to that Customer
Left() to get the first two letters

You said you know how to get the random numbers, but I don't understand how that ties in with the Account Number. How many letters is the Acc Number going to be made up of?

 

[gemma-the-husky]

louisa - you dont want to do this.

what you do want is to use "hidden" autonumber keys to connect your tables. eg customers and orders, Then you can give your customer a "visible" ACCOUNT NO which can be anything you like, and can be changed without causing any data issues at all.

THen you could even create an account with ref no like this before they place an order, and then update the account no after the place the order.

before
DB ABCDEF 00000

after
DB ABCDEF 100627



BUt going further in a database its easy to retrieve the date of the first order (or even store it in the customer table as another field). I can't see any benefit at all in building the date into the Account Ref.

 

[vbaInet]

I can't see any benefit at all in building the date into the Account Ref.

Neither can I

 

[louisa]

Thanks for all your help and advise, i will look into the date issue that you have advised and i have worked out how to get the two characters from a postcode.