autofill sequence in a table?

[merlin777]

I'm creating a table with times of the day from 08:00 to 17:00 in 15 minute increments.

I'm goig to use the table as a lookup to make it easier to enter an appointment time.

I don't really want to type them all by hand - does access have an equivalent to excel's drag and autofill a sequence? is it just as easy todo it in excel then cut and paste it?

 

[merlin777]

I tried cutting and pasting from excel and it worked fine.

'08:00' etc filled the field as 36 new records were created. I changed the data type to date/time and they reformatted to '08:00:00'

However, I went to set this up as the lookup source for my main table and i got an error saying this would only work with numbers or text, so I changed it to numbers and the lookup worked. I tried reformatting it to get rid of the seconds by going to design view/general tab/format 08:00 which it changed t 'short time', saved and returned to data sheet view and the seconds were still there.

I'm also going to be using this dta as time so i don't really wanted the data type as numbers.

is there any way around these little issues?

 

[Galaxiom]

Cast the times to strings in a derived field of a query and use that for your lookup.

textfield: CStr([timefield])

 

[merlin777]

Cast the times to strings in a derived field of a query and use that for your lookup.

textfield: CStr([timefield])

I'm so sorry Galaxiom, that's a bit technical for me! I think I understand that i have to use a query as the source of the lookup instead of a table but not certain about 'Cast the times to strings in a derived field'

could you bear to elaborate for me please?

 

[Galaxiom]

In the query designer enter the following in one of the field cells.

textfield: CStr([timefield])

Change [timefield] to the name of your field and textfield to something appropriate.

Then use the textfield as the lookup.

 

[namliam]

1) Dont use lookups in tables, you use them as comboboxes on forms

2) Access doesnt have a drag function like excel does, it does have a Cartesian query which kindoff is the same but in this case a lot more complex as well.

 

[Galaxiom]

1) Dont use lookups in tables, you use them as comboboxes on forms

Although I wouldn't leave a lookup on a table in a production database they are quite useful in the early phase because they will automatically produce a combo with the desired structure automatically when using the form wizard.