insert empty date string or date placeholder - vba

[Gr3g0ry]

hi guys, so im trying to use a date placeholder in access - vba but to no avail. ive tried finding help online via searches but nothing.

ive already declared my date variable and now want to assign a place holder like 00/00/0000 to it.

 

[isladogs]

Can you provide some context so its easier to provide an answer.
In other words, supply the section of code you want the date placeholder in.

 

[Gr3g0ry]

TRN = Me.cboTrn.Value
StartDate = Me.StartDate.Value
EndDate = Me.EndDate.Value
MaturityDate = Me.MaturityDate.Value
PartnerId = Me.PartnerId.Value
StartAmount = Me.StartAmount.Value
ActualBalance = Me.ActualBalance.Value
MaturitytBalance = Me.MaturityAmount.Value
Installments = Me.Installments.Value
PaymentMethod = Me.cboTerm.Value
Active = 1

DoCmd.RunSQL "INSERT INTO PARTNER (TRN,StartDate,EndDate,StartAmount,MaturityDate,ActualBalance,MaturityBalance,PaymentMethod,PartnerId,Installments,Active) VALUES ('" & TRN & "', #" & StartDate & "#, #" & EndDate & "#, " & StartAmount & ", #" & MaturityDate & "#, " & ActualBalance & ", " & MaturityBalance & ", '" & PaymentMethod & "', " & PartnerId & ", " & Installments & ", " & Active & ");"


EndDate is an disabled datepicker. so no value will be returned to be used in my query. the database field is a date field

 

[arnelgp]

so what date do you want to put in there?
same as maturity date?

maybe you could code it inside StartDate_AfterUpdate Event.

 

[Gr3g0ry]

yeah i kno i could use the AfterUpdate event.

is there a way to get a date like 00/00/000. the end date is to be entered at a late date when the client decides to end his partner plan. the partner runs for 6 months so his maturity date is =dateAdd("m", 6, me.StartDate.Value).

i just need a placeholder value.

 

[isladogs]

You can't enter 00/00/0000 as its not a valid date

You can either leave the field empty by omitting that section of the query

DoCmd.RunSQL "INSERT INTO PARTNER (TRN,StartDate,EndDate,StartAmount,MaturityDate,ActualBalance,MaturityBalance,PaymentMethod,PartnerI d,Installments,Active) VALUES ('" & TRN & "', #" & StartDate & "#, " & StartAmount & ", #" & MaturityDate & "#, " & ActualBalance & ", " & MaturityBalance & ", '" & PaymentMethod & "', " & PartnerId & ", " & Installments & ", " & Active & ");"

or set its value like this:

Dim dteEndDate As Date

dteEndDate = #10/27/2017#

DoCmd.RunSQL "INSERT INTO PARTNER (TRN,StartDate,EndDate,StartAmount,MaturityDate,ActualBalance,MaturityBalance,PaymentMethod,PartnerI d,Installments,Active) VALUES ('" & TRN & "', #" & StartDate & "#, #" & dteEndDate & "#, " & StartAmount & ", #" & MaturityDate & "#, " & ActualBalance & ", " & MaturityBalance & ", '" & PaymentMethod & "', " & PartnerId & ", " & Installments & ", " & Active & ");"

BTW CurrentDB.Execute is more efficient than DoCmd.RunSQL

 

[arnelgp]

Add another txtbox or label control with the value of 00/00/0000. Place it over the enddate control. On current event of the form make its visible property to no if the enddate field is not blank, yes if it is blank.