Alan

Remove the RowSource from the control source and place this code in your Form OnLoad Event.

Method 1:

Dim strSQL As String
strSQL = "SELECT [HighSystolic_LapTQ].[TargetDate], [HighSystolic_LapTQ].[S] FROM HighSystolic_LapTQ;"

If =DCount("[HighSystolic_LapTQ]", , strSQL) < 1 Then
     Exit Sub
Else If
     ListBox.RowSource = strSQL
End If

__________________________________________________________________________________________

Method 2:

On Error Resume Next

Dim strSQL As String
strSQL = "SELECT [HighSystolic_LapTQ].[TargetDate], [HighSystolic_LapTQ].[S] FROM HighSystolic_LapTQ;"

ListBox.RowSource = strSQL
__________________________________________________________________________________________

Hope this Helps,

-Raymond

Thanks Raymond however neither worked, i still get the same error. I have 2 lines of code i need to run. The calculation works however if no data is present i am getting the Syntax error. Thank you i really appreciate the help. I can make it work just by adding a record but i would like to figure this out if possible.

SELECT [HighDiastolic_LapTQ].[TargetDate], [HighDiastolic_LapTQ].[D] FROM HighDiastolic_LapTQ;
SELECT [HighSystolic_LapTQ].[TargetDate], [HighSystolic_LapTQ].[S] FROM HighSystolic_LapTQ

Alan  The error shows a field name called "BPreading" but where is it?  None of the SQLs you've posted has "BPreading" in it.

Sorry that should have been  "systolic " not  BPreading

Alan  The error message says the query that causes the error has an "=" sign, which means it has a criteria.  None of the SQLs you've posted has criteria.  Please post the one that has it.

I have queries that provide the date and highest BP number in the date range selected.

No worries this data is fake only test

Alan

Your query refers to a field [S]..... is this correct?  Should this be [Systolic]?

                                                                                               \/
SELECT [HighSystolic_LapTQ].[TargetDate], [HighSystolic_LapTQ].[S] FROM HighSystolic_LapTQ;

============================================================

BTW... you can also change this to:

SELECT TargetDate, S FROM HighSystolic_LapTQ;

-Raymond

Alan

Kevin is on the right track here.  The error is tell you that one of your fields has 'Systolic='

You need to find this field and correct this:

Either remove the '='... or add the field or reference you intended.

-Raymond

Its part of the query for the ListBox

Alan  DMax() needs a criteria for the third argument.  Is "PatDate" a criteria?  Any non-zero value (doesn't have to be -1) equals true.  More importantly, if DMax() doesn't find any record, it returns null.  That makes [S] null, and causes an error in Systolic=.

PatDate is the date for record entered into the BP table. I've attached a screen shot of the query.

Thanks Kevin i understand what you are saying however i am not sure how to give S a value if there are not records.

I am a novice with access, i am retired and work with access to challenge myself.  i took most of the classes up to access expert level however to become proficient you have to work with it every day on a professional level which i am not.

Alan  We are ALL novices in something: I am, Richard is, everyone is, because no one knows everything about anything, and everyone knows little about certain subjects.  The point is we can all relate.

The first thing is to fix your DMax, because "PatDate" is not a valid criteria.  A criteria should have an "=" sign, just like your other criteria: Systolic=

If [S] is null, you can use the Nz() function to assign it a value when it is null.

So i deleted the queries and code took a few SQL beginner courses  and came up with one query and the code below and all is well, i get the result i am looking for and no errors. Thank you both for helping get to where i needed to be.

SELECT
    BPT.PatDate,
    BPT.Systolic
FROM
    BPT
WHERE
    ( ((BPT.Systolic) = (SELECT Max(Systolic) FROM BPT )));