如何格式化VBA中的查询列？ [英] How to format query columns from VBA?

问题描述

大家好。


现在是午餐时间，所以在我2小时的会议之前，我可以花几分钟时间提出问题。


有人可以简单地向我展示如何从VBA设置一个按照我想要的方式格式化列的查询吗？


你看，我的大型多数据库查询工作正常很好。 VBA代码构建SQL字符串，它将查询各种表和UNION结果。然后使用 CreateQueryDef 在数据库中创建新查询，然后 OpenQuery 打开它。这一切都运行正常，性能相当不错。但是结果的格式化并不是真的可以接受。


我想到的两个选项是

• 在创建querydef后查看字段定义，看看是否我可以相应地设置各种属性。也就是说，像列宽和几个时间字段，格式为？hh：nn：ss？ （可能已经过时了？我需要在一段时间后查询有关此内容的内容时查看内容。）
• 不是每次都从头开始创建新的querydef，而是查询模板使用适当的格式，然后复制并每次修改SQL。

有什么想法吗？

解决方案

如果你真的想控制格式化包括列宽，我建议创建一个TableDef并将结果插入到新表中。


另一种选择是创建一个查询设置，假设字段没有改变，返回所有记录，并使用以下代码将参数传递给查询以获得您想要的结果

展开 | 选择 < span class =codeDivider> | Wrap | 行号

如果你真的想控制格式包括列宽，我建议创建一个TableDef并将结果插入到新表中。


另一种选择是创建一个查询设置以返回所有记录assumi字段不要更改并使用以下代码将参数传递给查询以获得您想要的结果

展开 | 选择 | 换行 | 行号

< blockquote>

嗯...我必须承认我并不热衷于创建表格的想法。特别是因为理论上这可以由多个用户同时运行，并且返回的集合可以是

我理解。这可能还会导致将参数传递给现有查询或重新定义现有查询时出现问题。可能您唯一的选择是创建qryDummy querydef并每次删除它。您可能需要为名称添加时间戳，除非每个用户都有自己的前端，您可以在那里创建它。


问题是我不知道如何控制列宽。


我过去使用的另一个选项是用户只需要查看结果就是使用列表框。


1.创建一个未绑定的列表框。

2.使用vba将行源设置为sql语句（查询中的联合查询格式化时间等）

3.设置列Count

4.设置列宽


示例：

展开 | 选择 | 换行 | 行号

Hi all.

Lunch time now, so I can spare a few minutes to throw in a question before my 2 hour meeting.

Can someone show me briefly how, from VBA, to set up a query with columns formatted the way I want?

You see, I have my big multi-database query thing working nicely. The VBA code builds the SQL string which will query the various tables and UNION the results. It then uses CreateQueryDef to create a new query in the database, then OpenQuery to open it. This all work OK, and performance is quite nice. But the formatting of the result is not really acceptable.

The two options I had in mind are

• Look through the field definitions after creating the querydef, and see whether I can set various properties accordingly. That is, things like column width and for a couple of time fields, a Format of ?hh:nn:ss? (that may be out of date ? I need to look up what was suggested on theScripts when I asked about this some time back).
• Rather than creating the new querydef from scratch each time, have a query template with appropriate formatting, which is then copied and the SQL modified each time.

Any thoughts?

解决方案

If you truely want to control formatting including column widths, I would advise creating a TableDef and inserting results into the new table.

Another option is to create a query set up to return all records assuming the fields don''t change and use the following code to pass parameters to the query to get the results you want

Expand|Select|Wrap|Line Numbers

If you truely want to control formatting including column widths, I would advise creating a TableDef and inserting results into the new table.

Another option is to create a query set up to return all records assuming the fields don''t change and use the following code to pass parameters to the query to get the results you want

Expand|Select|Wrap|Line Numbers

Hm... I must admit I''m not keen on the idea of creating a table. Especially since this could, theoretically, be run by a number of users simultaneously, and the returned sets could be

I understand. This might also cause a problem in passing a parameter to or redefining an existing query. It might be that your only option is to create a qryDummy querydef and delete it each time. You might have to timestamp the name unless each user has their own frontend and you could create it there.

The problem is I don''t know how you would control column width.

One other option which I''ve used in the past where users only needed to view results is using a listbox.

1. Create an unbound listbox.
2. Using vba set the row source to sql statement (your union query formatting time etc. in the query)
3. Set the column Count
4. Set the column widths

Example:

Expand|Select|Wrap|Line Numbers

这篇关于如何格式化VBA中的查询列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！