help wanted, calculated field (1 Viewer)

[AljayB]

Hello all,

I am seeking help with a format for a calculated field which can be seen in the attachments

I use the calculation [field1] & [field2] & [field3] & [field4] & [field5]
field 1 is a number, field 2 is a date, field 3,4 and 5 are numbers.

The calculated field displays the following format
1/1/1/2015/2/3/4
with a format @\/@@@@@@@@@\/@\/@\/@
:banghead:

The format that I would like to display in my calculated field is
1/010115/2/3/4

1 being the field 1 number,
010115 being the date ddmmyy,
2 (field3)
3 (field4)
4 (field5)


Many thanks in advance!

 

[plog]

So, you've got it all working except for the date? Right? For that you should use the format function (http://www.techonthenet.com/access/functions/date/format.php). Specifically what you want is this:

Format([YourDateField],"ddmmyyyy")

 

[AljayB]

Hello,

Thank you for the reply,

where do I insert that now in so that I can create the desired format?

Sorry I am a total newbie

 

[plog]

I use the calculation [field1] & [field2] & [field3] & [field4] & [field5]

Substitute it for the appropriate field in the above.

 

[cyanidem]

Specifically what you want is this:

Format([YourDateField],"ddmmyyyy")

I think he actually needs

Format([YourDateField],"ddmmyy")

So calculation will look like:

[field1] & Format([YourDateField],"ddmmyy") & [field3] & [field4] & [field5]

 

[plog]

The format that I would like to display in my calculated field is
1/010115/2/3/4

cyan is right.

 

[AljayB]

Thank you all !!!!!!

 

[AljayB]

what if I would like to make a button on a form in VBA that when clicked would generate the same combination in a text box field, which data will then be stored in my table.

could you guys please help me with the code?

The format that I would like to display in the combined text box is
1/010115/2/3/4

(field1)
(field2) 010115 being the date ddmmyy,
field3)
(field4)
field5)

combined box from the 5 fields.

if I make combined box = field1 +"/" + field2 "+" field3.....
doesnt work,

could someone please tell me how?

many thanks in advance.

 

[arnelgp]

combined box = (field1 + "/") & (field2 + "/") & (field3 + "/") & (field4 + "/") & (field5)

 

[AljayB]

thank you,

One more question,
how can I then formate the date in VBA to be displayed as a number value in the combined box calculation that you have posted?
like 01/02/15 , by 01 being the day, 02, being the month and 15 being the year.
field 2 being formatted from 2/1/2015 to 020115 in the combined box.

 

[arnelgp]

combined box = (field1 + "/") & Format(field2, "ddmmyy\/") & (field3 + "/") & (field4 + "/") & (field5)

 

[AljayB]

Private Sub btnCharge_Click()
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim resoult As Integer
a = Me.cmbProduktgruppeID
b = Me.txtChargeDatum
c = Me.cmbLinie
d = Me.cmbAbfulung
e = Me.cmbExtruder
resoult = (a + "/") & Format(b, "ddmmyy\/") & (c + "/") & (d + "/") & (e)
Me.txtcharge2 = resoult
End Sub


this now shows me a msg that says overflow, and it points to the b = Me.txtChargeDatum

all the problems I have at the moment are because of the damn datum...

 

[arnelgp]

hehe, your funny.
what is the value of Me.txtChargeDatum, is it date or numeric?
because in your btnCharge_Click, you declared it as Integer:

dim b As Integer

declare it as variant, then cast it as Date

Dim b As Variant

b = Me.txtChargeDatum
b = CDate(b)

remember to declare the correct datatype as with your control. if your control holds integer then declare it as such.

 

[AljayB]

I am sorry for all the questions, but thank you for your help....
I am completely new to vba hope u understand...

now the message shows me run time error 13, type mismatch...:banghead:

attached the file.

 

[arnelgp]

remove the line, and see what happens. we already declare it as variant, i think it will not complain now.

 

[AljayB]

still shows the error even if I rewrite it

 

[arnelgp]

remove this line:

b = CDate(b)

 

[AljayB]

still shows the same error even when I removed the line.

b = Me.txtChargeDatum is a date in my table.

 

[arnelgp]

is it possible for you to upload your db.

 

[AljayB]

please add me on google
aljaz.but22@gmail.com