Left function on a form

[jane30]

Hi, On my Main menu, there are two txtbox, StartDate and EndDate. Both should have default value derived from a select query (select only one record from field [Stat Per] in a table: "Jul99-Jun00". Data type is Text) I want StartDate to have a default value: 199907, EndDate: 200006. I've tried to use Exp1:left([Stat Per],5) in the select query. But it returns me an error msg:Ambiguous expression in your query. Then I changes the field name from Stat Per to Stat. It still doesn't work.

My question is What's wrong? And how can I convert "Jul99-Jun00" to two separate value: 199907, 200006?

Thanks a bunch in advance

 

[ImpMan]

I think you have a couple of problems.

1. If you are working with dates it is always best to have the format of the column set 'date'. If this is the case the information that is stored in such a field is a datenumber not a text string. You never see this number but you just have to get access to convert what ever you give it so the number is created. You have to give it a complete date (day,year,month). Are your start, finish fields date format? If not try and make this so.

2. It looks like you are entering the default date as an expression into the design of the field, this is the wrong place for it. It needs to be in the form.

3. If your record already has some information in it (in one of its fields) that someone has entered then the record is not a new record so the default value will not get entered. However you could enter it from a form event or a button click event. If you want to format the string date "Jul99-Jun00" the run the code below: The column the information goes into must be a date format though. If this is not the case this will not work. If you have a string format then you will have to change this.

Place this in the modules section

Function FormatDate(stgText As String) As Date
Dim intday As Integer
Dim stgmonth As String
Dim intyear As Integer
intday = 1
stgmonth = Left(stgText, 3)
intyear = Mid(stgText, 4, 2)
FormatDate = intday & "/" & stgmonth & "/" & intyear
End Function

Then add to a the form, I have mine triggered from a button.

Private Sub Button1_Click()
txtstart.Value = FormatDate(TXTdate)
End Sub

Where txtDate has Jul99-Jun00 displayed, and txtStart is the field you want the start date to go.

Hope this helps