Form Filter

[June7]

You don't want VBA to evaluate the expression (except for concatenating variables that provide parameters). The VBA is just to build a string that will be passed to the Filter property of the form or report. Then Access via the form or report will evaluate the expression. The only function I have ever used this way is IsNull. Examples:

Me.Filter = "IsNull(Submit.DateOut)"
Me.FilterOn = True

Me.Filter = "IsNull(Submit.DateOut) AND Submit.DateEnter < #" & DateAdd("ww", -2, Date) & "#"
Me.FilterOn = True


It's like using VBA to build an SQL statement. Constructed SQL string can be used to set RecordSource property of a form/report or can be executed in VBA to open a recordset or used with QueryDefs to modify an Access query object. Any functions included in the construct will be processed by the target object.

[ssanfu]

I have never use a function in the filter property. I have always built the string in VBA and set the filter property to the string.

Looking at the code in post #6, the filter string does not have a field name in the string. It is a string value = a string value and a string value = a text box value.
I would think there should be a field name in there somewhere.

But this won't be the last time I am wrong......
I'll have to look at it some more....

[June7]

I am building a string in VBA and setting the filter property. The string just happens to include a function.

The OP's criteria does have fieldname but the fieldname is wrapped in the StringFromGUID function, just as I wrap fieldname in the IsNull function. I could manually type IsNull(Submit.DateOut) into the form's Filter property in DesignView and achieve the same result as using VBA to set property in run-time.

I could wrap the field in other functions, such as:

Me.Filter = "Year(Submit.DateOut)=2012"


I just don't understand the OP's need to convert these values.

[ssanfu]

Me.Filter = "Year(Submit.DateOut)=2012"

Aah, I think I understand now. (I hope )

[RayMilhon]

Lots to respond to after the weekend.

1. In Access (at least 2007 and 2010 not sure about before that) When you link to a SQL Server Table that has a field with a uniqueidentifier datatype the Access Field is numeric and under format is replicationID.
2. Me.txtPCPName was originally a textbox However Requirements were changed and it then required a combo box. However the control name was used in several VBA Locations so the name was not changed. I have a comment at the top of each module using that field name to explain it. We do have plans to correct this however it works as is and this is supposed to go live this week and the only issue we're having is that last week we had to change the filter that worked to this more complicated filter and it's not working.

3. SSANFU I will try your suggestion and post back here later this morning.

[RayMilhon]

Thanks SSANFU I know what's happening just not sure what the fix is. When I step through the the PCP portion of the filter looks like this ???????? = "????????" The first part of the filter is what I expect stringfromguid([memb_keyid]) = "{DD1867B1-5052-418E-8367-BE2797EC8A3B}" It's the PCP_KEYID that is wrong just not sure why.

[RayMilhon]

Well I've figured it out

In the Recordset rs!Prov_keyid = {3A29C50B-0330-11D7-9D84-0060CF215F8B}
Which when using the watch on the recordset is showing up as Datatype Variant.
the stringfromguid(txtPCPNAME) is showing up as {guid {3A29C50B-0330-11D7-9D84-0060CF215F8B}}
If I take the stringfromguid(txtPCPNAME) and remove the {guid from the front and the last } then set the filter to the new string it works perfectly Thanks a lot for your help

[ssanfu]

uniqueidentifier datatype the Access Field is numeric and under format is replicationID.

I've just started to get into SQL Server & Express 2005 so I'm not very familiar with them, but when I up-size Access tables, the Access the autonumber (Long - PK) is converted to a SQL Server int Identity = TRUE.

In Access there is a replicationID type (I have never used) that allows multiple copies of a mdb to be merged into one master mdb without PK conflicts because of the GUID (globally unique ID) type.

Glad you got it straightened out.

[RayMilhon]

In SQL Server the Globally Unique ID Type is known as the UniqueIdentifier Data Type they are the same thing.
Also the Identity = True functions the same as the Autonumber with 1 difference in SQL Server you can specify a starting number and an incremental number say you want to start with 1000 and increment by 5 I've never tried doing that with the Access Autonumber but I don't believe you can. If anyone knows differently please chime in.

There are a number of similarities between SQL Server Data Types and Access Data types but they're not always obvious. For example in Access the Yes/No field is equivalent to the boolean datatype in SQL Server. Both SQL Server and Access have various numeric formats but some are named differently as in the ReplicationID and UniqueIdentifer.