| | Нашел небольшой FAQ
Формирование строки SQL-запроса в VBA
Аксесс позволяет создавать программно строки SQL-запросов, а также строки, служащие значениями свойств форм, отчетов и т.д., параметров функций и т.д., которые тоже должны соответствовать синтаксису SQL. При этом надо помнить, что входящие в такую строку константы должны быть тоже оформлены по правилам SQL, а именно:
в числах десятичным разделителем должна быть точка,
строки должны быть заключены в кавычки или апострофы (а кавычки или апострофы соответственно внутри строки удвоены),
даты должны быть заключены в решетки и написаны по американскому формату (mm/dd/yyyy) с дробной чертой в качестве разделителя.
Если это нарушить, то возможны следующие эффекты:
число вида 10,5 с запятой вместо точки будет воспринято как список из двух чисел 10 и 5, что приведет к какому-нибудь несоответствию в количестве полей,
строка вида Vasya без кавычек и апострофов будет воспринята как имя поля, если такое поле есть, или как имя параметра, который тут же будет запрошен,
дата вида 1/2/2010 или 1-2-2010 без решеток будет воспринята как арифметическое выражение (с делением и вычитанием соответственно),
дата вида 1.2.2010 будет воспринята как дробное число с двумя десятичными точками и приведет к ошибке,
дата с решетками, но не по американскому формату будет воспринята как дата, но другая (день и месяц будут переставлены).
Ниже в каждом разделе приведен пример строки SQL, которая должна получиться при ее программном создании, а затем код VBA, который ее создает. Еще ниже дана очень полезная рекомендация.
1. Использование чисел
SQL
SELECT *
FROM Table
WHERE (((Table.Quanty)=12.197));
VBA v1
Dim q As Single
q = 12.197
strSQL = "SELECT * " _
& "FROM Table " _
& "WHERE (((Table.Quanty)=" & q & "));"
VBA v2
Dim q As String
q = "12,197"
strSQL = "SELECT * " _
& "FROM Table " _
& "WHERE (((Table.Quanty)=" & Str(q) & "));"
Примечание:
VBA v1 - для целых чисел. Для дробных чисел это частный случай, только когда системным разделителем является точка.
VBA v2 - более правильный вариант, т.к. в классическом программировании допускается соединять только строки со строками, тогда как VBA v1 использует неявное преобразование типов, хотя нареканий на этот способ для целых чисел не было. (Пример приведён для случая, когда системным разделителем является запятая.)
Иногда также используют для преобразования функцию CStr(), но она не всегда применима, т.к. возвращает число в виде строки, где оно записывается через системный разделитель, тогда как SQL в качестве разделителя воспринимает только точку.
NB! При использовании нетипичных системных разделителей вышеприведённые примеры могут не работать, в этих случаях надо программно заменять системный разделитель на точку. Ниже приводится функция, возвращающая системный разделитель.
Function GetDecimalSeparator() As String
GetDecimalSeparator = Format(0#, ".")
End Function
Также следует учесть, что в этом случае некоторые стандартные действия Access могут не работать.
2. Использование строк
SQL
SELECT *
FROM Table
WHERE (((Table.Name)="All"));
VBA v1
Dim q As String
q = "All"
strSQL = "SELECT * " _
& "FROM Table " _
& "WHERE (((Table.Quanty)=""" & DoubleQuote(q) & """));"
VBA v2
Dim q As String
q = "All"
strSQL = "SELECT * " _
& "FROM Table " _
& "WHERE (((Table.Quanty)='" & DoubleApostrophe(q) & "'));"
Примечание:
VBA v1: DoubleQuote() - функция, удваивающая кавычки.
Пример:
условие выборки:
a"a's
SQL:
WHERE field="a""a's"
VBA:
strWhere = "WHERE field=""" & "a""""a's" & """"
VBA v2:: DoubleApostrophe() - функция, удваивающая апострофы.
Пример:
условие выборки:
a"a's
SQL:
WHERE field='a"a''s'
VBA:
strWhere = "WHERE field='" & "a""a''s" & "'"
Упомянутые выше функции DoubleQuote и DoubleApostrophe - это НЕ встроенные функции Аксесса, а пользовательские функции, реализация которых оставляется на усмотрение программиста. В частности, в Аксессе версий 2000 и выше можно для этой цели использовать встроенную функцию Replace, а в 97 и ниже - вот такую функцию:
Public Function Replace97(StrMain As String, StrFind As String, StrZam As String) As String
On Error GoTo err
Dim pos As Long
If StrFind = "" Then GoTo err
If StrMain = "" Then Replace97 = StrZam: Exit Function
Do Until InStr(1, StrMain, StrFind) = 0
pos = InStr(1, StrMain, StrFind)
StrMain = mid(StrMain, 1, pos - 1) & StrZam & mid(StrMain, pos + Len(StrFind), Len(StrMain))
Loop
Replace97 = StrMain
Exit Function
err:
Replace97 = StrMain
End Function
3. Использование дат
SQL
SELECT *
FROM Table
WHERE (((Table.TimeOpen)=#3/31/2003 11:17:19#));
VBA
Dim q As Date
q = Now
strSQL = "SELECT * " _
& "FROM Table " _
& "WHERE (((Table.TimeOpen)=#" & Format(q, "mm\/dd\/yy hh\:mm\:ss") & "#));"
Примечание:
Microsoft JET SQL оперирует датами в американском формате, т.е. именно в вышеуказанном виде Месяц/День/Год.
Не пропустите символы # (он обрамляет всю константу типа дата-время) и \ (он предохраняет / и : от их замены в соответствии с региональными настройками, что имеет обыкновение делать функция Format и что мешает правильной работе команды SQL).
Рекомендуется почитать здесь: http://www.sql.ru/faq/faq_topic.aspx?fid=103 о способах хранения даты/времени.
NB! Не стоит пользоваться преобразованием даты в Integer (или Long), т.к. в Access'е и в SQL Server'е одной и той же дате соответствуют разные числа и при сравнении можно получить неожиданный результат.
А теперь очень полезная рекомендация
Составив такой стринг, содержащий команду SQL, и отправив его на выполнение, вы можете получить ошибку. В таком случае распечатайте этот стринг в отладочное окно и посмотрите на него глазами. Возможно, ошибка сразу бросится в глаза. Если не бросится, создайте новый запрос в конструкторе запросов, переключите в режим SQL, вставьте туда текст запроса и запустите на выполнение. Если ошибка есть, то она будет показана более явным образом. | |
