Report on custom date field

[2jk7860]

I'd like to create a custom report for assets that have a date filled into our tblassetcustom.Custom1 field but are older than 2 weeks from today's date. 

My current query is as follows but it returns a listing of assets I would expect and assets that don't have anything in that field at all. I've tried doing not null for the criteria but that doesn't help. How do I query that custom date field to only show items older than 2 weeks from today's date and ignore those assets that don't have anything in that custom field? Thanks in advance for any help you can offer!!

Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypename,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Lastseen,
tblassets.Lasttried,
tblassetcustom.Custom1
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Where tblassetcustom.Custom1 < DateAdd(week, -2, GetDate())

[KevinA-REJIS · Accepted solution]

You can add 

tblassetcustom.Custom1 <> ''

to the Where statement, that will bring up only assets with a value in that custom field.

I think the issue with the date comparison is that Lansweeper stores the value in the field as a string, which can't be compared with a date. I tried to find a way to convert the string to a date but didn't have much luck. 

[2jk7860]

Thanks for providing that information, this definitely gives me valid information and I can look to build upon it. I appreciate the help!!

[ASismey]

Hi , Have you tried something like 

Where tblassetcustom.Custom1 >= GetDate() - 14

[2jk7860]

Thanks for the reply, however I'm looking for assets with that field filled in and dates older than 14 days. Even with changing the greater than to a less than, it still returns all the assets that have a blank custom1 field. I need to eliminate those empty fields from the results. Thanks!!