Extract field if contains lowercase and date/time check

[Tazlady34]

1) If I have a field that is formatted as alphanumeric and can also have special characters...is there a way to extract that field if it contains any lowercase letters



2) Have a field in access that is date/time...how do I have it check and verify the date is the end of the month.


Thanks in advance

 

[Russell Hauf]

For the first one, I think that you'll need to write a function. Go to the Macros tab, click New, and paste in the following:

Function HasLowerCase(strText As String) As Boolean
    HasLowerCase = StrComp(strText, UCase(strText), vbBinaryCompare)
End Function

Then, if you want to extract the records that have lower case in a query, you can put:
HasLowerCase([fieldToCheck])
in a new query field, and in the Criteria for that field, put: True (you can uncheck the "Show" checkbox).

As to your second question, if you want to find dates that are NOT the last day of the month, you can put (again in a blank query field/column): Day([YourDateField]+1), and in the criteria for that field, put: <> 1 (if you want records that have the last day of the month in the date field, put: = 1 instead of <> 1).

The SQL for the queries might look like this (change table and field names - my table is named tbl110603, and my field names are fldText and fldDate):

1:

• SELECT tbl110603.fldText
  FROM tbl110603
  WHERE (((HasLowerCase([fldText]))=True));

2:

• SELECT tbl110603.fldText, tbl110603.fldDate
  FROM tbl110603
  WHERE (((Day([fldDate]+1))<>1));

Hope this helps,

Russell

 

[Tazlady34]

Thank you...