Display calculated field from query in a subform

[nur annie]

HI ALL,

I HAVE A FORM CALLED "SEARCH" THAT I WANT TO DISPLAY CERTAIN INFO FROM THE CONTROL "MONTH". IN MY MAIN DATABASE (INFOTBL) I HAVE DOB FIELD WHICH THE DATA TYPE IS DATE/TIME. IN A QUERY(INFOTBL QUERY) I CREATED A FIELD WITH EXP: MONTH:FORMAT([DOB],"M") TO GET THE VALUE FOR MONTH THAT I WANT TO USE IN MY SEARCH FORM. THE RESULT SHOULD BE DISPLAY IN A SUBFORM WITHIN THE 'SEARCH FORM' AND THE SUBFORM SOURCE COME FORM INFOTBL QUERY. MY PROBLEM IS THAT I CAN'T MANIPULATE THE VALUE MONTH IN THE QUERY AND THE RESULT SHOW IN THE SUBFORM FOR THE FIELD MONTH IS #Name?

CAN ANYBODY HELP ME WITH THIS

THANKS IN ADVANCE
ANNIE

 

[sxschech]

Thanks for providing your database.

Try this...

In your form infoTBL Query subform go to design view.

Click on the text box for Month (the box that currently is showing #NAME?)

If the properties window isn't open, make it visible by clicking on the Property Sheet Icon from the menu bar.

Type this into the Control Source

=Format([DOB],"mmm")

This will show up on your form as Nov for November. If you want it spelled out completely, add one more m

For more information about Format, you can press the F1 key and search for Format, scroll down to the section Custom format placeholders and separators and it will show the various letter codes to format dates and times.

 

[nur annie]

thanks sxschech...i'l try this immediately...

annie

 

[nur annie]

hi sxschech,

thanks for the tips..the value appear in the subform as i wanted... but the main thing is i need to manipulate the value for filtering in my search form... when i enterend the value in the unbound text box for month in the search form, a dialog appeared "enter parameter value" for month...

is there something wrong with my vba code...

thanks
annie

 

[sxschech]

Hi Annie,

Looks like we have to make a few changes to your code. You can either copy and paste this over your existing code, or incorporate the lines that are bold. Seems we have to convert the letter month to a number --> Nov becomes 11 in order to evaluate the month against your date of birth field. I created a generic date for being able to get the function to work, the only part that matters is the month so the day and year won't impact the final comparison.

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim tmp As String
    
    [I][B]Dim stMonth As String[/B][/I]
    
   tmp = """"[B]
    [I]stMonth = Me.txtMonth & "/01/9999"[/I][/B]
    
    Const ConJetDate = "\#dd\/mm\/yyyy\#"
    
    varWhere = Null
    
    If Me.txtID > "" Then
     varWhere = varWhere & "[ID] like " & Me.txtID & " AND "
    End If
    
    If Me.txtName > "" Then
     varWhere = varWhere & "[Name] like " & tmp & Me.txtName & tmp & " AND "
    End If
    
    If Me.txtDateFrom > "" Then
      varWhere = varWhere & "([DOB] >= " & Format(Me.txtDateFrom, ConJetDate) & ") AND "
    End If
    
    If Me.txtDateTo > "" Then
    varWhere = varWhere & "([DOB] <= " & Format(Me.txtDateTo, ConJetDate) & ") AND "
    End If
    
    If Me.txtMonth > "" Then
       [I] [B]varWhere = varWhere & "Month([DOB]) like " & Month(stMonth) & " AND [/B]"[/I]
    End If
    
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = " Where " & varWhere
        
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    
    BuildFilter = varWhere
    
End Function

 

[nur annie]

Hi sxschech

I've altered the code as per your sample but it return null value/new record in the subform...??? did u get this result as well. can you help me with this, i'm not very good in programming.

thanks
annie

 

[sxschech]

Hi Annie,

I am able to add new record as well as search without an error, could you tell me the steps you went through which caused the error?

Pat,

The code I provided has the Month function.

varWhere = varWhere & "Month([DOB]) like " & Month(stMonth) & " AND "

The reason for the strings is that I was trying to display the result as "Nov" in the last col, and also allow the user to type the word Nov into the search box. Since Nov is a string, had to convert it back to a date.

 

[nur annie]

Hi sxschech...

i understand now why my query doesn't goes well with the code... thanks a lot...you save the day...

Pat,
thanks for the tips...

Annie...