Query to delete records with values of "--"

Dear all,

I have a table imported into Access the structure is below.  There are 7 consecutive dates.

ID  Latitude  Longitude  19/12/2014  20/12/2014  21/12/2014 etc.

I wrote the below query and it works for that dataset, i.e. the next dataset will have dates for the following week.  Is it possible to write a query to check and delete records where all fields except e.ID, e.Latitude, e.Longitude contain "--"  (without including field names)?

Thanks

SELECT e.ID, e.Latitude, e.Longitude, e.[19/12/2014] AS Expr1, e.[20/12/2014] AS Expr2, e.[21/12/2014] AS Expr3, e.[22/12/2014] AS Expr4, e.[23/12/2014] AS Expr5, e.[24/12/2014] AS Expr6, e.[25/12/2014] AS Expr7 INTO FormattedData
FROM Export_FFDI_Valid_Python AS e
WHERE ((([e].[19/12/2014])<>"--") AND (([e].[20/12/2014])<>"--") AND (([e].[21/12/2014])<>"--") AND (([e].[22/12/2014])<>"--") AND (([e].[23/12/2014])<>"--") AND (([e].[24/12/2014])<>"--") AND (([e].[25/12/2014])<>"--"));

Open in new window

AndyC1000Asked:
Who is Participating?
 
aikimarkCommented:
Correction.  I forgot to square bracket the field names.
Sub Q_28627100()
    Dim fld As Field
    Dim strSQL As String
    Dim strPattern As String
    For Each fld In DBEngine(0)(0).TableDefs("Export_FFDI_Valid_Python").Fields
        If IsDate(fld.Name) Then
            strSQL = strSQL & " & [" & fld.Name & "]"
            strPattern = strPattern & "--"
        End If
    Next
    strSQL = Mid(strSQL, 4)
    strSQL = "Delete * From Export_FFDI_Valid_Python Where " & strSQL & " = " & strPattern
    DBEngine(0)(0).Execute strSQL
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
delete *
from Export_FFDI_Valid_Python
where instr([19/12/2014] & [20/12/2014] & [21/12/2014] & [22/12/2014] & [23/12/2014] & [24/12/2014] & [25/12/2014], "--") > 0
0
 
Dale FyeCommented:
because the dates (column headings) of this table are going to change from week to week, you are going to have to build this query at run time.  Personally, I would use a function to return the string, and would call it with something like:

strSQL = fnDeletePython(#19/12/2014#)

then the function would look like:
Public Function fnDeletePython(StartDate as Date) as String

    Dim dtLoop as Date
    Dim strSQL as variant
    
    For dtLoop = StartDate to DateAdd("d", 6, StartDate)
        strSQL = strSQL & "& [" & Format(StartDate, "dd/mm/yyyy") & "] "
    Next
    strSQL = Mid(strSQL, 3)
    strSQL = "DELETE FROM Export_FFDI_Valid_Python " _
                & "WHERE instr(" & strSQL & ", " & chr$(34) & "--" & chr$(34) & ") > 0"
    fnDeletePython = strSQL

End Function

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
Actually, upon re-reading your post, I believe what you want is a string that looks like:

DELETE FROM Export_FFDI_Valid_Python
WHERE [19/12/2014] LIKE "*--*"
AND [20/12/2014] LIKE "*--*"
AND ...

The function for that would look like:
Public Function fnDeletePython(StartDate as Date) as String

    Dim dtLoop as Date
    Dim strSQL as variant
    
    For dtLoop = StartDate to DateAdd("d", 6, StartDate)
        strSQL = strSQL & "AND ([" & Format(StartDate, "dd/mm/yyyy") & "] " _
                     & "LIKE " & chr$(34) & "*--*" & chr$(34) & ") "
    Next

    strSQL = Mid(strSQL, 5)
    strSQL = "DELETE FROM Export_FFDI_Valid_Python WHERE " & strSQL
    debug.print strSQL
    fnDeletePython = strSQL

End Function

Open in new window

0
 
Gustav BrockCIOCommented:
The simplest thing you could do, is to export the table without the field names, then attach this:

DoCmd.TransferSpreadsheet  acExport, ,"Export_FFDI_Valid_Python", "c:/test/FFDI.xlsx", False
DoCmd.TransferSpreadsheet acLink, ,"xlsFDDI", "c:/test/FDDI.xlsx", False

This attached table has generic field names: F1, F2, F3, F4, etc.

Now create a query:

Select
    F1 As ID,
    F2 As Latitude,
    F3 As Longitude,
    F4 As Date1,
    F5 AS Date2,
    F6 AS Date3,
    F7 AS Date4,
    F8 AS Date5,
    F9 AS Date6,
    F10 AS Date7
From
    xlsFDDI
Where
    IsDate(F4) And IsDate(F5) And IsDate(F6) And IsDate(F7) And IsDate(F8) And IsDate(F9) And IsDate(F10)

Then use this query as source for your further processing.

Of course, you might be able to attach your original file without the field names, thus skipping the two first steps.

/gustav
0
 
AndyC1000Author Commented:
In reference to the duplicate question I was looking at two possible solutions. I started with this question whether it was possible to write a generic query to check if any of the fields contain "--", if so delete the entire record.  The original data comes from a netcdf file -  I know that if one of the dates contain "--" it means there are no records for it.  

A netcdf file is downloaded from a server weekly and the python script is used to create the csv which is imported into Access.    

The solutions provided on this question will work, however I'm trying to avoid users altering the date fields.  If I'm wrong please let me know.

The python question was posted after I decided it might be easier not to write those rows of data to the file in the first place.  I'm new to python and haven't been able to get the code to enter the if block where the check is , I.e.

if "masked" not in content: # also tried "--"
   outputwriter.writerow(content)
   print content

Open in new window

Please advise if I should delete the python question as I'm unable to add a python tag to this one.
0
 
AndyC1000Author Commented:
To clarify this question the below code block writes a csv file.   My issue is when there is no data in the content string for a lat/lon, the row is outputted to the csv file as

 [-33.939999, 151.03918, --, --, --, --, --, --, --]

 or to the console as

 [-33.939999, 151.03918, masked, masked, masked, masked, masked, masked, masked]

 I tried wrapping the outputwriter around an if statement, it doesn't enter the code block. i.e.

 if "masked" not in content: # also tried if "--" not in content
    outputwriter.writerow(content)
    print content

with open(r'C:/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    for date_val in date_strings:
        header.append(date_val)
    outputwriter.writerow(header)
    for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            for time_index, time in enumerate(times[:]): 
                data = value[time_index,lat_index,lon_index]
                content.append(data)
            outputwriter.writerow(content) 

Open in new window

0
 
aikimarkCommented:
This routine will construct and execute the delete statement, based on your criteria.
Sub Q_28627100()
    Dim fld As Field
    Dim strSQL As String
    Dim strPattern As String
    For Each fld In DBEngine(0)(0).TableDefs("Export_FFDI_Valid_Python").Fields
        If IsDate(fld.Name) Then
            strSQL = strSQL & " & " & fld.Name
            strPattern = strPattern & "--"
        End If
    Next
    strSQL = Mid(strSQL, 4)
    strSQL = "Delete * From Export_FFDI_Valid_Python Where " & strSQL & " = " & strPattern
    DBEngine(0)(0).Execute strSQL
End Sub

Open in new window

0
 
clockwatcherCommented:
Looks like you're probably dealing with a numpy masked array. If you are, you should be able to test for a mask and if it has one, not include it in your csv.  Give the following a try:
import numpy as np
from numpy.ma import getmask
with open(r'C:/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    for date_val in date_strings:
        header.append(date_val)
    outputwriter.writerow(header)
    for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            skip_row = False
            for time_index, time in enumerate(times[:]): 
                data = value[time_index,lat_index,lon_index]
                if np.any(getmask(data)):
                    skip_row = True
                else:
                    content.append(data)
            if not skip_row:
                outputwriter.writerow(content) 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.