Help with code: =[Day] & "........."

[StefanSch]

Hello there

I would like to have a text box in a form show the following.

Monday.........................................

Therefore I tried to add the following as the text box source.

=[Day] & ".........................................."

However, this does not work. Do you have any ideas how to achieve that?

Thanks a lot. Kind regards,

Stefan

 

[jjturner]

I don't see a problem with your ControlSource assignment. I tested it and this worked fine for me.

What is happening when you try to set your expression as you did?

 

[StefanSch]

Hello JJTurner

It actually says

#Error

Stefan

 

[StefanSch]

Could it be a problem that the expression

=[Day] & ".........................................."

is in a continuous form?

regards, Stefan

 

[jjturner]

I'm afraid you have me stumped on this I actually did get the #Name? error when I based your expression on another calculated control.

First off, I wouldn't recommend using 'Day' as your field or control name. Access has a built in 'Day' function (which would make it confusing if you were using both a field named 'Day' and the 'Day' function).

Secondly, I'm hazarding a guess that Access doesn't allow concatenation of a string value on one control with a string calculated from a another control.

If 'Day' is a control name on your form and its value is calculated on the form, you'll have to repeat that same calculation expression in the textbox where you need the "...." after it:

=Format(Date(),"dddd") & "........"

HTH,
John

 

[StefanSch]

Thanks a lot for your helpful answer.

What would be your code suggestion if the variable would be called ABCD?

e.g. =[ABCD] & ".........................................."

Thank you and regards,

Stefan

 

[jjturner]

Stefan,

Please elaborate as to whether this 'variable' as you call it is a field name in a table or query, or a control name on your form.

The point I was trying to make was that if [ABCD] is a control name on your form and is calculated on the form, then I don't think you can refer to it in an expression for another control on your form. On your other control (the textbox that you want to display "Monday......." on), you would have to repeat the same expression you used to get the value for [ABCD] and concatenate that with your string - "......."

ControlSource for the "Monday......" textbox would be:

=(expression used to derive value for [ABCD]) & "......."

This is only if [ABCD] is getting calculated on your form - NOT if it's getting calculated in the query, or already exists as a value in the table. (In either of these latter cases, I don't see why you would be getting the error with your original expression.)

HTH,
John

 

[StefanSch]

jjturner,

this 'variable' is a field name in a table.

Thank you so much for your helpful support.

regards,

Stefan

 

[StefanSch]

For your information. The text box only works right now if the ControlSource is set as following:

ABCD

If I set it to

=[ABCD],

MS Access shows #Error in the text box.

Do you have any ideas?

regards, Stefan

 

[jjturner]

Yikes!

Sorry, but I'm plumb out of ideas on this one. This is gonna take an exorcist or one of the Access masters to figure out. When and if they find the solution, we might be both hitting our heads on the desk because it's probably something simple

Best Regards,
John

 

[raskew]

You might try playing with this. Function Padem(), which I use when creating formatted text files, will pad a string with whatever character you specify, either to the left or to the right.

Function padem(ByVal pstr As String, pchar As String, plen As Integer, pdir As Boolean) As String
'pdir: true = left justify; false = right justify
Dim strSQL As String
Dim reps As Integer
Dim i As Integer

strSQL = ""

'determine number padded of characters required
reps = plen - Len(pstr)

For i = 1 To reps
   strSQL = strSQL & pchar
Next i

'place the padded string to the left or right
'based on the value of pDir
strSQL = IIf(pdir = True, pstr & strSQL, strSQL & pstr)

padem = strSQL

End Function

After copying/pasting the above to a new module in Northwind, try running the following query. It's intended to identify the weekday of OrderDate and display it as "Monday........." or "Saturday......." or whatever, with each string being exactly 15 characters in length.

If that works for you then it shouldn't be too much of a jump to use it as the source for your text box.

Please post back.

SELECT Orders.OrderID, Orders.OrderDate, padem(Format(Day([OrderDate]),"dddd"),".",15,True) AS MyPadem
FROM Orders;

Best wishes,

Bob