update query based on dates

[Lapchien]

done homework on Usenet and still stuck...

I have a parameter query that asks the user to input the 'slip reference'
based on the accounting period. This has now changed slightly so that the
slip reference number is based on the date, so for instance if the date is
as follows, the slip reference is as follows:

J12345 21/08/2003
J12346 22/08/2003
J12347 23/08/2003
J12348 24/08/2003
J12349 25/08/2003
J12350 26/08/2003
J12351 27/08/2003
J12352 28/08/2003
J12353 29/08/2003
J12354 30/08/2003
J12355 31/08/2003
J12356 01/09/2003
J12357 02/09/2003
J12358 03/09/2003
J12359 04/09/2003
J12360 05/09/2003
J12361 06/09/2003
J12362 07/09/2003
And so on...

So the user doesn't have to enter a slip ref

I have a table called 'slips' just like the example above, filled up with
slip ref's and dates to cover a year or so. In a table I have a field
called ec_vat_num, I'd like this field populated with the slip ref based on
the input date (again I have a field in this table with the input date
already filled up) and run from a query.

I'm struggling with the access update syntax. Can anyone assist? Thank you.

Lap


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

[Lapchien]

thanks
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"HJ" <hj******@hotmail.com> wrote in message
news:3f***********************@dreader6.news.xs4al l.nl...

If the input date has already been entered, you may use an inner join to
solve your problem.

UPDATE ec_vat_table INNER JOIN tblSlip ON ec_vat_table.inputdate =
tblSlip.slipDate SET ec_vat_table.ec_vat_num = [slipSlipRef];

HJ

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message
news:10*************@ananke.eclipse.net.uk...

done homework on Usenet and still stuck...

I have a parameter query that asks the user to input the 'slip reference' based on the accounting period. This has now changed slightly so that the slip reference number is based on the date, so for instance if the date is as follows, the slip reference is as follows:

J12345 21/08/2003
J12346 22/08/2003
J12347 23/08/2003
J12348 24/08/2003
J12349 25/08/2003
J12350 26/08/2003
J12351 27/08/2003
J12352 28/08/2003
J12353 29/08/2003
J12354 30/08/2003
J12355 31/08/2003
J12356 01/09/2003
J12357 02/09/2003
J12358 03/09/2003
J12359 04/09/2003
J12360 05/09/2003
J12361 06/09/2003
J12362 07/09/2003
And so on...

So the user doesn't have to enter a slip ref

I have a table called 'slips' just like the example above, filled up with slip ref's and dates to cover a year or so. In a table I have a field
called ec_vat_num, I'd like this field populated with the slip ref based

on

the input date (again I have a field in this table with the input date
already filled up) and run from a query.

I'm struggling with the access update syntax. Can anyone assist? Thank

you.

Lap


--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

[Marc]

Lap,

I think you could write a simple function in VBA to generate the 'slip-reference':

Public Function slipref(Optional InputDate As Date = 0) As String
Dim result As String
Dim datevalue As Date
If InputDate = 0 Then datevalue = Date Else datevalue = InputDate
result = "J" & Format(CLng(Int(datevalue) - 25509), "00000")
End Function

By declaring the function public it also can be used in a SQL-expression.
When no parameter is given, the actual slipref will be returned.

Marc

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message news:<10*************@ananke.eclipse.net.uk>...

done homework on Usenet and still stuck...

I have a parameter query that asks the user to input the 'slip reference'
based on the accounting period. This has now changed slightly so that the
slip reference number is based on the date, so for instance if the date is
as follows, the slip reference is as follows:

J12345 21/08/2003
J12346 22/08/2003
J12347 23/08/2003
J12348 24/08/2003
J12349 25/08/2003
J12350 26/08/2003
J12351 27/08/2003
J12352 28/08/2003
J12353 29/08/2003
J12354 30/08/2003
J12355 31/08/2003
J12356 01/09/2003
J12357 02/09/2003
J12358 03/09/2003
J12359 04/09/2003
J12360 05/09/2003
J12361 06/09/2003
J12362 07/09/2003
And so on...

So the user doesn't have to enter a slip ref

I have a table called 'slips' just like the example above, filled up with
slip ref's and dates to cover a year or so. In a table I have a field
called ec_vat_num, I'd like this field populated with the slip ref based on
the input date (again I have a field in this table with the input date
already filled up) and run from a query.

I'm struggling with the access update syntax. Can anyone assist? Thank you.

Lap

[Marc]

Lap,

I think you could write a simple function in VBA to generate the 'slip-reference':

Public Function slipref(Optional InputDate As Date = 0) As String
Dim result As String
Dim datevalue As Date
If InputDate = 0 Then datevalue = Date Else datevalue = InputDate
result = "J" & Format(CLng(Int(datevalue) - 25509), "00000")
End Function

By declaring the function public it also can be used in a SQL-expression.
When no parameter is given, the actual slipref will be returned.

Marc

"Lapchien" <cc****@NOSPAMeclipse.co.uk> wrote in message news:<10*************@ananke.eclipse.net.uk>...

done homework on Usenet and still stuck...

I have a parameter query that asks the user to input the 'slip reference'
based on the accounting period. This has now changed slightly so that the
slip reference number is based on the date, so for instance if the date is
as follows, the slip reference is as follows:

J12345 21/08/2003
J12346 22/08/2003
J12347 23/08/2003
J12348 24/08/2003
J12349 25/08/2003
J12350 26/08/2003
J12351 27/08/2003
J12352 28/08/2003
J12353 29/08/2003
J12354 30/08/2003
J12355 31/08/2003
J12356 01/09/2003
J12357 02/09/2003
J12358 03/09/2003
J12359 04/09/2003
J12360 05/09/2003
J12361 06/09/2003
J12362 07/09/2003
And so on...

So the user doesn't have to enter a slip ref

I have a table called 'slips' just like the example above, filled up with
slip ref's and dates to cover a year or so. In a table I have a field
called ec_vat_num, I'd like this field populated with the slip ref based on
the input date (again I have a field in this table with the input date
already filled up) and run from a query.

I'm struggling with the access update syntax. Can anyone assist? Thank you.

Lap