“#Error” in related field before entering data in main field

[masoud_sedighy]

Hello



In my query I have used built in function and parameter of this function is another field of my query.


Now in my form before entering anything in textbox1, textbox2 shows “#Error”, when I finish entering and left textbox1 and set focus on textbox2 my function operates and results is ok.

How I can solve problem of “#Error” in related field.

Best regards.

[ssanfu]

Hello

In my query I have used built in function and parameter of this function is another field of my query.


Now in my form before entering anything in textbox1, textbox2 shows “#Error”, when I finish entering and left textbox1 and set focus on textbox2 my function operates and results is ok.

How I can solve problem of “#Error” in related field.

Best regards.

You didn't post the SQL of the query, say what function or the parameter values so it is hard to be specific. One thought would be to use the IIF() function to check that a parameter is not NULL or zero. Another would be to use the NZ() function on each parameter to change a NULL to a different value.

[masoud_sedighy]

Hello

My sql query is like below and
My main field is:[Due Date]
Related field: Farsi_Due_Date

And my function is M2SDATE (Date Parameter)

I have used “M2SDate (Tasks_Recieved![Due Date]) AS Farsi_Due_Date” in the query.

Now in the form when I want to start data entry “Farsi_Due_Date” shows “#error” until I entered data in [Due Date] and get focus on another field “#error” will be removed.


sql:

SELECT Tasks_Recieved.ID_Recieved, Tasks_Recieved.Sender, Tasks_Recieved.ID__RecievedDate, Tasks_Recieved.Documentcode, Tasks_Recieved.Title, Tasks_Recieved.DocumentDate, Tasks_Recieved.Priority, Tasks_Recieved.Status, Tasks_Recieved.[% Complete], Tasks_Recieved.[Assigned To], Tasks_Recieved.Description, Tasks_Recieved.[Due Date], M2SDate(Tasks_Recieved![Due Date]) AS Farsi_Due_Date, Tasks_Recieved.Attachments, Tasks_Recieved.ID_Send
FROM Tasks_Recieved;


My module:
Public Function M2SDate(DocumentDate As Date)


Dim ifday, ifmonth, ifYear, ifdayOfyear As Long
Dim iyear, idayOfyear As Long
Dim iNumDayOfyear As Long
Dim aifMonthDays



aifMonthDays = Array(31, 31, 31, 31, 31, 31, 30, 30, 30, 30, 30, 29)
iNumDayOfyear = 365
Debug.Print DocumentDate
iyear = Year(DocumentDate)

idayOfyear = DatePart("Y", DocumentDate)
If isly(iyear - 1) Then
iNumDayOfyear = 366
aifMonthDays(11) = 30
End If

If (idayOfyear > 79) Then
ifYear = iyear - 621
ifdayOfyear = idayOfyear - 79
Else
ifYear = iyear - 622
ifdayOfyear = (iNumDayOfyear - 79) + idayOfyear
End If


ifday = ifdayOfyear
While (ifday > aifMonthDays(ifmonth))
ifday = ifday - aifMonthDays(ifmonth)
ifmonth = ifmonth + 1
Wend
ifmonth = ifmonth + 1
M2SDate = ifYear & "/" & ifmonth & "/" & ifday


End Function
Function isly(nyear)
isly = (((nyear Mod 4) = 0 And (nyear Mod 100) <> 0) Or (nyear Mod 400) = 0)
End Function

Best regards.

[ssanfu]

Try the following. My change is in BLUE...

sql:
   
  SELECT Tasks_Recieved.ID_Recieved, Tasks_Recieved.Sender,  Tasks_Recieved.ID__RecievedDate, Tasks_Recieved.Documentcode,  Tasks_Recieved.Title, Tasks_Recieved.DocumentDate,  Tasks_Recieved.Priority, Tasks_Recieved.Status, Tasks_Recieved.[%  Complete], Tasks_Recieved.[Assigned To], Tasks_Recieved.Description,  Tasks_Recieved.[Due Date],  IIf(IsDate([Due Date]),M2SDate([Tasks_Recieved]![Due Date]),"") AS Farsi_Due_Date, Tasks_Recieved.Attachments, Tasks_Recieved.ID_Send
  FROM Tasks_Recieved;

Also, in this line:

Dim iyear, idayOfyear As Long

iyear is a variant and idayOfyeat is a long. If you want both to be longs, use:

Dim iyear As Long, idayOfyear As Long

Same here:

   Dim ifday As Long, ifmonth As Long, ifYear As Long, ifdayOfyear As Long