More VB/Access/SQL syntax problems

  • Thread starter Thread starter Tony K
  • Start date Start date
T

Tony K

George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS Queries.
Some problems I have use CONVERT in SQL but I don't know the appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type, action_type,
customer_id, supplier_id, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id, supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20), trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id, inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101), inventory_transactions.location_id,
inventory_transactions.user_id, sites.site_name,
location.code


Thank you,
Tony K
 
Figured it out!! I realized I am converting a string to a date with these
SQL statements. The correct usage instead of CONVERT is DateValue( "text to
convert to a date").

Thanks Jack.

Tony K
 
No, you are converting from date to char.

Figured it out!! I realized I am converting a string to a date with these
SQL statements. The correct usage instead of CONVERT is DateValue( "text to
convert to a date").

Thanks Jack.

Tony K



Tony K said:
George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS Queries.
Some problems I have use CONVERT in SQL but I don't know the appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type, action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id, supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20), trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id, inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
 
Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




Jack Jackson said:
No, you are converting from date to char.

Figured it out!! I realized I am converting a string to a date with these
SQL statements. The correct usage instead of CONVERT is DateValue( "text
to
convert to a date").

Thanks Jack.

Tony K



Tony K said:
George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS Queries.
Some problems I have use CONVERT in SQL but I don't know the appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id, inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




Jack Jackson said:
No, you are converting from date to char.

Figured it out!! I realized I am converting a string to a date with these
SQL statements. The correct usage instead of CONVERT is DateValue( "text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS Queries.
Some problems I have use CONVERT in SQL but I don't know the appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id, inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
I'm converting a data type of CHAR 20 characters in length to trans_date
which is a date and the format 101 is U.S. format mm/dd/yyyy. How can MSDN
say that is wrong?? Also, you'll notice the original SQL 2005 syntax shows
"AS transaction_date" as the column name. Thanks, but I am right Jack.

Anyone else agree or disagree??

Tony K.

Jack Jackson said:
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




Jack Jackson said:
No, you are converting from date to char.

On Mon, 10 Sep 2007 18:04:31 -0600, "Tony K"

Figured it out!! I realized I am converting a string to a date with
these
SQL statements. The correct usage instead of CONVERT is DateValue(
"text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS
Queries.
Some problems I have use CONVERT in SQL but I don't know the
appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id
=
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
CONVERT([CHAR](20), trans_date, 101) converts the field trans_date to
CHAR(20), formatting it as a U.S. style mm/dd/yyyy date. The result
of the CONVERT is a CHAR(20). The field trans_date could be either a
date or a string type. Your column transaction_date is a CHAR(20).

The first argument to CONVERT is the data type of the result. There
would be no need to supply the data type of the expression being
converted, because that is whatever it is.


I'm converting a data type of CHAR 20 characters in length to trans_date
which is a date and the format 101 is U.S. format mm/dd/yyyy. How can MSDN
say that is wrong?? Also, you'll notice the original SQL 2005 syntax shows
"AS transaction_date" as the column name. Thanks, but I am right Jack.

Anyone else agree or disagree??

Tony K.

Jack Jackson said:
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx
Click to expand...
I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




No, you are converting from date to char.

On Mon, 10 Sep 2007 18:04:31 -0600, "Tony K"

Figured it out!! I realized I am converting a string to a date with
these
SQL statements. The correct usage instead of CONVERT is DateValue(
"text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS
Queries.
Some problems I have use CONVERT in SQL but I don't know the
appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON inventory_transactions.location_id
=
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type, inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
Since you asked.
Yes, the date field is being converted to a char or string datatype.
To give you a little more insight - The date was being converted as a way
to strip out the time portion of the date. And that was necessary because
ofthe group by clause, which would have otherwise grouped by each unique
date/time. Try running the query without the convert and see the result
set.
remember to remove the convert in the select and group by clauses.

HS



Tony K said:
I'm converting a data type of CHAR 20 characters in length to trans_date
which is a date and the format 101 is U.S. format mm/dd/yyyy. How can
MSDN say that is wrong?? Also, you'll notice the original SQL 2005 syntax
shows "AS transaction_date" as the column name. Thanks, but I am right
Jack.

Anyone else agree or disagree??

Tony K.

Jack Jackson said:
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




No, you are converting from date to char.

On Mon, 10 Sep 2007 18:04:31 -0600, "Tony K"

Figured it out!! I realized I am converting a string to a date with
these
SQL statements. The correct usage instead of CONVERT is DateValue(
"text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have
some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS
Queries.
Some problems I have use CONVERT in SQL but I don't know the
appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON
inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2) AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
Very well. I will stand down on this issue. I apologize if I've ticked you
off. It looks like I was confused on this issue.

Tony K.


Jack Jackson said:
CONVERT([CHAR](20), trans_date, 101) converts the field trans_date to
CHAR(20), formatting it as a U.S. style mm/dd/yyyy date. The result
of the CONVERT is a CHAR(20). The field trans_date could be either a
date or a string type. Your column transaction_date is a CHAR(20).

The first argument to CONVERT is the data type of the result. There
would be no need to supply the data type of the expression being
converted, because that is whatever it is.


I'm converting a data type of CHAR 20 characters in length to trans_date
which is a date and the format 101 is U.S. format mm/dd/yyyy. How can
MSDN
say that is wrong?? Also, you'll notice the original SQL 2005 syntax
shows
"AS transaction_date" as the column name. Thanks, but I am right Jack.

Anyone else agree or disagree??

Tony K.

Jack Jackson said:
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


On Mon, 10 Sep 2007 18:54:45 -0600, "Tony K"

Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and
found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




No, you are converting from date to char.

On Mon, 10 Sep 2007 18:04:31 -0600, "Tony K"

Figured it out!! I realized I am converting a string to a date with
these
SQL statements. The correct usage instead of CONVERT is DateValue(
"text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have
some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS
Queries.
Some problems I have use CONVERT in SQL but I don't know the
appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON
inventory_transactions.location_id
=
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2)
AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
Very well. I will stand down on this issue. I apologize. It looks like I
was confused on this issue.

Tony K.

HSalim said:
Since you asked.
Yes, the date field is being converted to a char or string datatype.
To give you a little more insight - The date was being converted as a way
to strip out the time portion of the date. And that was necessary because
ofthe group by clause, which would have otherwise grouped by each unique
date/time. Try running the query without the convert and see the result
set.
remember to remove the convert in the select and group by clauses.

HS



Tony K said:
I'm converting a data type of CHAR 20 characters in length to trans_date
which is a date and the format 101 is U.S. format mm/dd/yyyy. How can
MSDN say that is wrong?? Also, you'll notice the original SQL 2005
syntax shows "AS transaction_date" as the column name. Thanks, but I am
right Jack.

Anyone else agree or disagree??

Tony K.

Jack Jackson said:
Sorry, but you are wrong. The first argument to CONVERT is the
resulting data type, which in your case is CHAR.


On Mon, 10 Sep 2007 18:54:45 -0600, "Tony K"

Nope. Look at the parenthesis. CONVERT([CHAR](20), trans_date, 101) AS
transaction_date

I am converting text to a date. I looked up the syntax on msdn and
found
the following.
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

The 101 represents mm/dd/yyyy.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx


I think I just jumped the gun and didn't know where to start looking but
your link did help. Thank you.

Tony K




No, you are converting from date to char.

On Mon, 10 Sep 2007 18:04:31 -0600, "Tony K"

Figured it out!! I realized I am converting a string to a date with
these
SQL statements. The correct usage instead of CONVERT is DateValue(
"text
to
convert to a date").

Thanks Jack.

Tony K



George was able to help me out last week. Thanks George. I have
some
convert problems. RECAP: I'm converting SQL VIEWS into ACCESS
Queries.
Some problems I have use CONVERT in SQL but I don't know the
appropriate
syntax in Access. Here are the queries I have causing problems.

The error I receive is: "Undefined function CONVERT in expression"
Any help would be greatly appreciated.

SELECT SUM(quantity) AS qty, line_item_id, trans_type,
action_type,
customer_id, supplier_id, po_id, invoice_id, company_id,
CONVERT([CHAR](20), trans_date, 101)
AS transaction_date
FROM inventory_transactions
WHERE (record_status = 4)
GROUP BY line_item_id, trans_type, action_type, customer_id,
supplier_id,
date_code, po_id, invoice_id, company_id, CONVERT([CHAR](20),
trans_date,
101)




SELECT SUM(inventory_transactions.quantity) AS qty,
inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101) AS transaction_date,
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name, location.code AS location
FROM ((inventory_transactions INNER JOIN
location ON
inventory_transactions.location_id =
location.location_id) INNER JOIN
sites ON location.site_id = sites.site_id)
WHERE (inventory_transactions.record_status = 1 OR
inventory_transactions.record_status = 2)
AND
(inventory_transactions.adjust_reason_id <> 11)
GROUP BY inventory_transactions.line_item_id,
inventory_transactions.trans_type,
inventory_transactions.action_type,
inventory_transactions.customer_id,
inventory_transactions.date_code,
inventory_transactions.po_id, inventory_transactions.invoice_id,
inventory_transactions.company_id,
CONVERT([CHAR](20),
inventory_transactions.trans_date, 101),
inventory_transactions.location_id, inventory_transactions.user_id,
sites.site_name,
location.code


Thank you,
Tony K
Click to expand...
Click to expand...
Click to expand...
 
Back
Top