Empty Crosstab Colums Killing Access 2007

Jul 25, 2007

I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:


Private Sub Text66_DblClick(Cancel As Integer)
Dim Units
Dim UnitsCache
Dim PackagesCache
Dim F4x5Cache
Dim F5x7Cache
Dim E8x10Cache
Dim E11x14Cache
Dim S16x21Cache
Dim T21x24Cache
Dim WalletsCache
Dim T4x5
Dim T5x7
Dim T8x10
Dim T11x14
Dim T16x21
Dim T21x24
Dim TWallets

If [4x5] = Null Then
T4x5 = 1
Else
T4x5 = [4x5]
End If

If [5x7] = Null Then
T5x7 = 1
Else
T5x7 = [5x7]
End If

If [8x10] = Null Then
T8x10 = 1
Else
T8x10 = [8x10]
End If

If [11x14] = Null Then
T11x14 = 1
Else
T11x14 = [11x14]
End If

If [16x21] = Null Then
T16x21 = 1
Else
T16x21 = [16x21]
End If

If [21x24] = Null Then
T21x24 = 1
Else
T12x24 = [21x24]
End If

If [Wallets] = Null Then
TWallets = 1
Else
TWallets = [Wallets]
End If



F4x5Cache = T4x5
F5x7Cache = T5x7
E8x10Cache = T8x10
E11x14Cache = T11x14
S16x21Cache = T16x21
T21x24Cache = T21x24

WalletsCache = TWallets 8
UnitsCache = T8x10 + T5x7 2 + T4x5 4

Text58 = 0
Text56 = " "
Text54 = " "
Text52 = " "

RerunPackages:
PackagesCache = "x"
If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "F"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[T21x24Cache] = [T21x24Cache] - 1
Text58 = Text58 + 439
GoTo FoundOne
End If

If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "E"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[S16x21Cache] = [S16x21Cache] - 1
Text58 = Text58 + 305
GoTo FoundOne
End If

If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "D"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
[E11x14Cache] = [E11x14Cache] - 1
Text58 = Text58 + 236
GoTo FoundOne
End If

If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "C"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
Text58 = Text58 + 169
GoTo FoundOne
End If

If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "B"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
Text58 = Text58 + 127
GoTo FoundOne
End If

If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then
PackagesCache = "A"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 2
Text58 = Text58 + 89
GoTo FoundOne
End If


FoundOne:
If PackagesCache <> "x" Then
If Text56 = " " Then
Text56 = PackagesCache
GoTo RerunPackages
ElseIf Text54 = " " Then
Text54 = PackagesCache
GoTo RerunPackages
ElseIf Text52 = " " Then
Text52 = PackagesCache
GoTo RerunPackages
End If
End If

UnitsCache = (T8x10 + T5x7 2 + T4x5 4) - UnitsCache
Do
If F4x5Cache < 4 Or UnitsCache = 0 Then
GoTo Skip4x5
End If
F4x5Cache = F4x5Cache - 4
UnitsCache = UnitsCache - 1
Loop
Skip4x5:
Do
If F5x7Cache < 2 Or UnitsCache = 0 Then
GoTo Skip5x7
End If
F5x7Cache = F5x7Cache - 2
UnitsCache = UnitsCache - 1
Loop
Skip5x7:
Do
If E8x10Cache < 1 Or UnitsCache = 0 Then
GoTo Skip8x10
End If
E8x10Cache = E8x10Cache - 1
UnitsCache = UnitsCache - 1
Loop
Skip8x10:

Text42 = 0
Redo:
If WalletsCache >= 5 Then
Text42 = [Text42] + 40
WalletsCache = [WalletsCache] - 5
GoTo Redo
End If
If WalletsCache = 4 Then
Text42 = [Text42] + 37
ElseIf WalletsCache = 3 Then
Text42 = [Text42] + 33
ElseIf WalletsCache = 2 Then
Text42 = [Text42] + 28
ElseIf WalletsCache = 1 Then
Text42 = [Text42] + 18
End If

Text28 = [F4x5Cache] * 12
Text30 = [F5x7Cache] * 25
Text32 = [E8x10Cache] * 40
Text34 = [E11x14Cache] * 110
Text36 = [S16x21Cache] * 155
Text38 = [T21x24Cache] * 249
Text40 = [25x31] * 362
Text44 = [16x20 Collage] * 210
Text46 = [Hard Cover w/Poetry] * 212
Text48 = [Hard Cover Book] * 98
Text50 = [Soft Cover Book] * 68

Text66 = Text28 + Text30 + Text32 + Text34 + Text36 + Text38 + Text40 + Text42 + Text44 + Text46 + Text48 + Text50 + Text58 - Text60

End Sub

View Replies


ADVERTISEMENT

This Is Killing Me...

May 22, 2006

Okay.

I have a main form TRequests, which has several subforms, although we are dealing with two here in particular.

I have one subform [bsdata] that has combo's to choose data and a [enter] button to submit.

This updates the second subform (query table) with a [PK] and further metadata...

I would like to have the [enter] key from the first subform, also open a pop-up [addnew] (no biggie) and enter the newly created [PK] in a field that I normally have to enter it in manually.

I would like to accomplish this before the data updates to the query table showing the results.

Pulling hair out.

View 2 Replies View Related

Dates Are Killing Me!

Jun 25, 2005

Thanks for the help. I'm getting through this db.
I am trying to get user input of start date and End date for the following:

SELECT [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code] AS [Q5 TCs], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date]
FROM [ASC Cust Count_Crosstab] INNER JOIN [Oracle Account TC] ON [ASC Cust Count_Crosstab].Node = [Oracle Account TC].Node
GROUP BY [ASC Cust Count_Crosstab].Node, [Oracle Account TC].[Q Code], [ASC Cust Count_Crosstab].Video, [Oracle Account TC].[Check In Date], [ASC Cust Count_Crosstab].ASC, [Oracle Account TC].[Q Code]
HAVING ((([Oracle Account TC].[Q Code])="5") AND (([Oracle Account TC].[Check In Date]) Between [Start Date] And [End Date]) AND (([ASC Cust Count_Crosstab].ASC)="uh"));


But I get a reply back "The Microsoft Jet database does not recognize '[Start Date]' as a valid field name or expression.
Could someone tell me what I'm doing wrong. They are date fields. Thanks.

View 4 Replies View Related

Access 2007 Crashes On Excel 2007 Import

Mar 21, 2008

On trying to import an Excel 2007 file into Access 2007 I get the following:

1) Choose "External Data," Import, Exce
2) Select small file in MS Excel 2007 format.
3) Click on Import the source data into a new table ...
4) Choose OK

Immediately get "Microsoft Office Access has stopped working. Windows is checking for a solution to the problem..." This message never resolves, so I have to click 'Cancel'.

Then I get "Microsoft Office Access is trying to recover your information..." I have to click "Cancel" here as well and then kill MS Access in Task Manager.

Event Viewer gives following message, which is of no help:
"ID: 2, Application Name: Microsoft Office Access, Application Version: 12.0.6211.1000, Microsoft Office Version: 12.0.6215.1000. This session lasted 710 seconds with 120 seconds of active time. This session ended with a crash."

Trying to import a small Excel 2003 file leads the same result. The same happens with a CSV file.

I have uninstalled and reinstalled Office 2007 as well. I am using Vista Ultimate.

I have turned off DEP.

How can I get MS Access 2007 to import Excel?

Thanks

Ken

View 6 Replies View Related

Access 2007 Runtime Not Visible In Access 2007

Nov 26, 2007

I installed Office Enterprise 2007. Then I installed DEveloper Exrtensions, then Access Runtime 2007. All appear to have installed OK - they appear in Program and Features.
Instructions on runtime packaging tell me that a Developer option should appear in the resulting drop-down when I click the Office button (top-left round thing) when I have a d/b open in Access. It's not there! I have un-installed and re-installed the extensions and the runtime - still nothing!
Help!
I do have Runtime 2002 (XP) on the same PC, is that relevant?

View 10 Replies View Related

Values From Different Colums....

Feb 19, 2008

Hi Gurus,
Here's my situation. I have three columns in Access:
Attribute Name, DateValue, CharValue.
I wish to create a make table query which does the following:
If Attribute Name = 'approved_date' then take the value from DateValue Column and populate it in a New table 'new'.
but if the
Attribute Name = 'description' the take the value from CharValue column and populate it in the same table 'new' in a different column. Please help, how to achieve this.

Thanks,
Kon

View 1 Replies View Related

How To Add Value To Blank Colums

Jul 12, 2005

I have a table which has a column and some of them are filled and some are blank. I want to fill the blank ones with the previous values, how do I do this. Thanks for any help..

View 4 Replies View Related

Rows To Colums

Jun 29, 2007

OK, here we go...... Is there a way to convert multiple rows to a single record. For example:

Rec# Existing Data
001 | 1000
002 | 2001
003 | 3252
004 | 6573
005 | 8983
006 | 9897

Is there a way to put this all in one record, like this:

Rec# . 1st# . 2nd# . 3rd# . 4th# . 5th# . 6th#
001 | 1000 | 2001 | 3252 | 6573 | 8983 | 9897

And can you do this from the query design view?

Just curious.

View 4 Replies View Related

Hide Colums Without Data

Jun 10, 2006

Is there a way to hide a column in a query that does not have any data in it?

I have tables that are populated with YES/NO boxes and I only want to bring back the information for fields that are YES. Currently, the queries run and bring back all fields.

Each query will only have 1 result as they are limited by the primary key.


Thanks!

View 1 Replies View Related

Merge Colums In A Query

Jan 30, 2008

I am making this harder than it has to be i'm sure. I had a previous post of auto-texting a field, well why can't I just take two fields I have, such as the ID field which is an autonumber field and combine that with a type field that I have which is D.

If I could merge this in a query, wouldn't it give me the results as D01, D02, etc?

I couldn't find anything helpful online, one side said do the following.

SELECT receivables2.type+receivables2.ID
FROM receivables2;

Only I get errors.

I also tried this.

SELECT merge(receivables2.type; receivables2.ID);
FROM receivables2;

But it says merge is an invalid expression.

Could someone please help?

View 6 Replies View Related

Block The Colums Width

Sep 25, 2006

I have a form displayed as data sheet, how can I block the column so that the final user will not modify the width of the columns?

M

View 1 Replies View Related

Summin Colums - Urgent

Nov 14, 2006

hi all

i am adding colums like

Col_1 + Col_2 + col_3 + ...so on

now when a field is empty/null there is error in result

how to overcome this please help

i am adding colums ( not SUM(col_1) )

thanks in advance

NMK

View 1 Replies View Related

Averages From Multiple Colums With Ignored Values

Feb 15, 2007

I have a table containing about 120 records of 40 fields containing integer values. The values are 0 (for 'no experience'), 1 - 5 (for evaluation of experience) and 9 (for question not answered). I would like to generate a row of averages for the 40 columns.

Access includes the '0's when using the Avg function. (So 1,0,3,0,1,4 yields 1.5 (1+0+3+0+1+4 / 6) rather than the accurate 2.25 (1+0+3+1+4 / 4)). I can tackle this in two ways: I either convert all zero's to NULLs, as Access will not count NULL in an Avg function call, or I can do each column in a seperate query using a WHERE clause. I also have the problem of screening out the 9's. I'm reluctant to create 40 queries and then another to amalgamate the results as this seems a very silly way to solve this problem. I cannot convert both the zeroes AND the 9's to NULL as to do so would lose valuable data.

Can anyone suggest how I can obtain a full row of averages for the 40 fields, ignoring 0's and 9's?

View 10 Replies View Related

Can Office Access 2003 Runtime-Based Solutions Applies To Access 2007?

Jan 29, 2007

Hello,

I have installed Microsoft Access 2003 and Microsoft Access 2003 Developer Extensions. I can create Access application by using the Package Wizard.
But after I upgrade from Microsoft Access 2003 to Microsoft Access 2007.
I can't use the Package Wizard.

How can I do to solve this?

Thanks.

View 1 Replies View Related

Table Problems Moving From Access 2003 -&gt; Access 2007

Jun 19, 2007

Attached is a screenshot of the relationships in a database I built a couple of years ago. It's worked absolutely fine in Access 2003 and currently has over 18,000 customers with associated information in it.

However, when I open the database in Access 2007 the performance is awful. All the forms are very slow to respond when tabbing between form elements. I've experimented by reducing the number of form elements calling on related data on a given page and whilst this improves performance it reduces usability - something I don't want to compromise on especially since Access 2007 should be able to cope with this.

My next question is therefore whether I've got the most efficient underlying table design and I can't see any other way of doing it than my current method so I'd be grateful for any feedback or advice anyone has.

View 2 Replies View Related

Combo List Empty - Access 2002 / 2003

Sep 29, 2007

Hello,

Since the last Windows XP update on the secretary PC, all combo lists are now empty even if the related tables are not at all.

Here is her configuration:
Windows XP SP 2
Access 2003 SP 3

Here is the description:

1) I've got a combo list based on the table Towns with zip-code, name, country.

2) In the form, in addition the combolist is sorted on the name, just to easily pick up the right Town

3) Since the Windows update, the combo list displays only the zip-code, the other fields are empty even if the table content is not empty at all; I mean I checked in the table content itself.

4) I created a query based on the same arguments of the combo list and the query is working well alone; if I try to create a new combo list linked to the query, then I get the same weird result

5) I've tried to create a new field with the combo list assistant; when I select the table Towns, it shows me the full content (so name, zip, country) and when I run the form, the combo list is again almost empty !!

6) What is more weird is that when I put the Access database back on my standalone laptop which is running Access 2002, the combo list is working correctly !! In fact I am currently maintaining the Access database from my Access 2002 since more than one year and I had no issue

7) In fact, I got the same issue with all the combo-list in my Database

8) I tried to compact the DB but with no effect

Somebody has a brillant idea for me ?? otherwise I think I will contact Mister Bill directly :)



Fab

View 3 Replies View Related

Access Sql Replace Statement Null/empty Strings

Jul 31, 2007

Im trying to concatenate a bunch of fields (50 arghh) which each are either blank or just contain one letter. This was someone elses setup for an attendance register which I think is an odd way of doing it. I would have used one field and then to get the mark for a week take a substring at the appropriate position. Anyway im trying to replace an empty string field with a letter to represent the register hasn't been marked using sql statement:

SELECT [400 Student Marks].acad_period, [400 Student Marks].student_id, [400 Student Marks].register_id, [400 Student Marks].register_group, Replace([1],"","U") AS attendance
FROM [400 Student Marks];

[1] being the first register week then I would have concatenated with [2] etc...

This however causes an error each time on the attendance field so im guessing replace doesn't work on empty strings. Is there a way round this/alternative.

Thanks for any advice.

View 1 Replies View Related

If Some Text Boxes Empty Dont Allow Other Fields Access

Jul 27, 2005

Hello Friends,

Thank you for all the help i have gotten over the past couple days, as you can tell i am new and seek somebodys experience and wisdom to solve my issue.


I have a simple Form, That allows the users to enter contact information.

At the very top I have "Name" and "Age" and below it i have other fields such as Address, city, state, etc...

How can I make it so the user does not have access to the other information until they type in BOTH fields "Name" and "age" first.

Can somebody please show me how the Code might look like.

Thank you for your time.

View 2 Replies View Related

Access 2003 To Access 2007 Split Database

Mar 8, 2007

Hi All,

Background Info: I developed our main department's Access 2003 split database which is on a server for 15+ Users. I've now been given Access 2007 for development -- Users still have 2003. There are also 2 other smaller databases that are not split (.mdb).

Problem: In the split database, I've saved the Application .mdb as 2003, relinked and made a new .mde. But the Users still cannot open the database. (I did this in a copy until I figure out the nuances with 2007.) The 2 other unsplit databases can be saved as 2003 version and Users can open OK.

I'm grateful for any suggestions on working with Access 2007. I've been trying to tackle the ribbon which is a whole other question.

View 4 Replies View Related

Problem Running Access 2007 And Access 2002

Mar 27, 2007

Having a problem here after installing 2007. All my previously created databases work fine in 2007 (after clicking the enable, which is a pain,but don't know any other way) When I return to 2002 and try to open the databases I get different errors all something like this on....... "The expression On Open you entered as the event property setting produced the following error: Object or class does not support the set of events. Whats up here?????I have found a site here...http://allenbrowne.com/bug-17.htmlMentions this problem,but refers to Vista..... Does the same apply to XP? And which is the best fix?

View 7 Replies View Related

Access 2003 Database Not Functioning In Access 2007

Mar 30, 2007

Hi

I have an Access 2000/2003 database which opens up and displays correctly in Access 2007, but when I go to use the File>Print command, or for that matter any of the other pull down menu's nothing happens?

Has anyone else experienced thisproblem, or does anyone know what I am doing wrong?

Any help greatly appreciated.

Thanks

Steve

View 1 Replies View Related

Forms :: Empty Combobox Values When Access Form Loads Vba

Feb 25, 2014

I want to empty the combobox every time when form loads. Using the below code

Quote:

Private Sub Form_Load()
combo1.RowSource = ""
End Sub

But Combobax is not emptying.

View 6 Replies View Related

Queries :: Word Recipient List Empty With Access Query

Sep 1, 2014

I have a query in Access and a word document that opens up the query. Currently the query contains every record in the database. If I alter the query in Access to select a certain set of records (based on dates) when Word opens the recipient list is empty. Checking for errors it says there were no records or no data records matched query options.

How can I get Word to open and use the modified query?

View 3 Replies View Related

Access 2007 And Access Developer Extension

Nov 29, 2006

Hello friends.

Do you know anything about a new Developer Extension for Access 2007 ?

Thank :) :)

View 1 Replies View Related

Access 2007 To Access 2000 Problem

Dec 17, 2007

I have created a database in Access 2007 that now works well, and one that I'm quite proud off. However, I can easily convert it to an Access 2000 format as can anyone else here, but when I load it up there is a major problem.

I have used the tab control in my main form and that has three tabs on it. However, it's positioned correctly in Access 2007 it's not positioned correctly in Access 2000 as I have to move the tab control down a bit, but that would seem fine. So I lowered the tab control form and changed my form layout slightly, but even then the tab control over all over the Access 2000 form whenever I interact with the tabs - most bizzare.

Any help? Thanks.

View 3 Replies View Related

Access VBA - Crosstab Tables Into Forms / Deleting Blanks

Aug 1, 2013

I have a crosstab table that is displaying in a form within a form because the form has data coming from multiple tables.

Crosstab Query looks like
Name Sum Cust1 Cust2 Cust3.....
Jon 50 250
Bill 100 50 100
Kevin 200 50 100
Ryan 10 500

The issue are the blanks in the rows for each Name. When I bring it up on the form, I want the columns to be the Customers but only customers with amounts. The original form is there to show the name and position and the form within the form should have 1 row for name of the person that is being selected and columns of customers WITH A BALANCE.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved