SQL如何获取上周的日期

满大人

1. 
   

复制代码

1. sql = "SELECT * FROM [sheet1$] WHERE 考试日期 >= '" & Format(DateAdd("d", -8, Date()), "yyyy/mm/dd") & _
   
2.            "' AND 考试日期 < '" & Format(DateAdd("d", -1, Date()), "yyyy/mm/dd") & "'"

复制代码

如图所示，每周一需要查询日期是上周的数据，上周的日期用VBA中的SQL该怎么写,这个代码运行不了

WANT-T

    Select Case 完成时间类型
    Case Is = "近7天" '近7天
        '当期
        start_Date = Format(Now() - 6, "yyyymmdd")
        end_Date = Format(Now(), "yyyymmdd")
        '上期
        start_Date_1 = Format(Now() - 13, "yyyymmdd")
        end_Date_1 = Format(Now() - 7, "yyyymmdd")
    Case Is = "近14天" '近14天
        '当期
        start_Date = Format(Now() - 13, "yyyymmdd")
        end_Date = Format(Now(), "yyyymmdd")
        '上期
        start_Date_1 = Format(Now() - 23, "yyyymmdd")
        end_Date_1 = Format(Now() - 14, "yyyymmdd")
    Case Is = "近30天" '近14天
        '当期
        start_Date = Format(Now() - 29, "yyyymmdd")
        end_Date = Format(Now(), "yyyymmdd")
        '上期
        start_Date_1 = Format(Now() - 59, "yyyymmdd")
        end_Date_1 = Format(Now() - 30, "yyyymmdd")
    Case Is = "本周" '本周
        start_Date = Format(Now() - Weekday(Now(), 2) + 1, "yyyymmdd")
        end_Date = Format(Now(), "yyyymmdd")
        '上期
        start_Date_1 = Format(Now() - Weekday(Now(), 2) - 6, "yyyymmdd")
        end_Date_1 = Format(Now() - 7, "yyyymmdd")
    Case Is = "本月" '本月
        '当期
        start_Date = Format(Now(), "yyyymm") & "01"
        end_Date = Format(Now(), "yyyymmdd")
        '上期
        start_Date_1 = Format(CDate(Format(Now(), "yyyy/mm/") & "01") - 1, "yyyymm") & "01"
        end_Date_1 = Format(DateAdd("m", -1, CDate(Format(Now(), "yyyy/mm/dd"))), "yyyymmdd")
    Case Is = "上月" '
        '当期
        start_Date = Format(CDate(Format(Now(), "yyyy/mm/") & "01") - 1, "yyyymm") & "01"
        end_Date = Format(CDate(Format(Now(), "yyyy/mm/") & "01") - 1, "yyyymmdd")
        '上期
        start_Date_1 = Format(CDate(Format(Now(), "yyyy/mm/") & "01") - 2, "yyyymm") & "01"
        end_Date_1 = Format(CDate(Format(Now(), "yyyy/mm/") & "01") - 2, "yyyymmdd")
    Case Is = "本年" '本年
        '当期
        start_Date = Format(Now(), "yyyy") & "0101"
        end_Date = Format(Now(), "yyyymmdd")
        '同期
        start_Date_1 = Format(Now(), "yyyy") - 1 & "0101"
        end_Date_1 = Format(DateAdd("m", -12, CDate(Format(Now(), "yyyy/mm/dd"))), "yyyymmdd")
    End Select

z865593849

查收。。。。。。。。